SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated 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.

 

Example:

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:

Expected Output:

Proposed Solution:

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

My Output will be:

 

Please make comments, if this helps you in any way

Read More

SQL SERVER: REPLICATE function

Today i read SQLAuthority.com, and I found that one developer has this issue. Lets see that problem as well as the solution for the same.

There is one numeric column. User needs to make sure that all data should be of same size. Like "17.00,12.00,8.17,4.44", these all should be "17.00,12.00,08.17,04.44" like that.

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE ("string that you want TO append" ,"INTEGER VALUE" )

FIRST parameter, i need TO SET the CHARACTER, which will append it BEFORE the NUMBER.

SECOND parameter, how many times this CHARACTER should be ADD TO the NUMBER.

Example:

DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one "0" to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions

Read More