Sunday, February 26, 2012

Combine multiple rows in a single column, Concatenate records without UDF


CREATE TABLE test
(
id INT, Categoryid INT, Category VARCHAR(50), Status VARCHAR(50)
)

------------------------------
INSERT INTO test(id,categoryid,category,[Status])
SELECT 34676 ,02 ,'CLOTHING' ,'ACTIVE'
UNION
SELECT 34676 ,02 ,'CLOTHING' ,'DANCE'
UNION
SELECT 34676 ,02 ,'CLOTHING' ,'LWR'
----------------------------------

There is two ways to implement this
1.
SELECT DISTINCT id,categoryid,category ,dbo.fnMurliTest(categoryid) As NewColumn From test

Alter function fnTest(@int int)
Returns varchar(8000) As
Begin
DECLARE @strOutput VARCHAR(8000)
SET @strOutput = ''
SELECT @strOutput = @strOutput + ',' + [Status] FROM test
WHERE [CategoryID] = @int ORDER BY [Status]
RETURN STUFF(@strOutput, 1, 1, '')
--RETURN SubString(@strOutput, 2, 8000)
End

2.
SELECT DISTINCT id,categoryid,category,
(
SELECT ',' + status as 'data()'
FROM test
FOR XML PATH('')
) As NewColumn
FROM test

1 comment:

  1. Good article.I really think what you said is right,in modern society,we have so many troubles,if you feel nothing to relax yourself,I suggest you to play the wow and some other games,and you can come to my page to find more information about WOW Gold and D3 Items

    ReplyDelete