Posts Tagged ‘SQL Comma seprated list’

h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List

Get Comma separated List

I want output like:

Get Comma separated List

Get Comma separated List

I come up with very good solution. Let me share with all of you:

Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

Advertisements
h1

Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

01/06/2009

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7.

I need to configure reports in manner, so User can access this reports thru External IP Address.

I found one interestign thing with IIS 7.0, so user can access reports externally.

To able to access the reports externally, do the steps as mentioned below:

1. Create Virtual Directory in IIS 7.0 :

Configure Reporting Services with IIS 7

2. Go to Handler Mappings :

Configure Reporting Services with IIS 7.0

3. Double Click on “Handler Mapping”. You will get screen like the given below:

Configure Reporting Service with IIS 7.0

4. Now see Top Right Corner: Click On “Add Managed Handler”. Here you need to fill up the info like:

Configure Reporting Services with IIS 7.0

Fill this info as:

Request path:      Reserved.ReportViewerWebControl.axd
Type:                    Microsoft.Reporting.WebForms.HttpHandler
Name:                  Reserved-ReportViewerWebControl-axd

Thats it. Now try with External IP.

You can access the reports with  External IP too.

h1

COALESCE – To Get Comma Separated List SQL SERVER

12/29/2008

Today in one report, i need to display the comma seprated data from table. So use the COALEASE feature and will share with you all.

You can use it like:

DECLARE @result VARCHAR(MAX)
SELECT @result = COALESCE(@result + ', ', '') + TestTejas.ColumnName
FROM
TestTejas

SELECT @result