Aggregate multiple values in a column into one column using SQL


A     banana
A     apple
A     guava
B      banana
B      apple
A      pear

To aggregate multiple column into one column, we can use the new(ish) with() statement:

WITH blah(myname,faves,lvl) as
SELECT name AS name,CAST(favorite AS NVARCHAR(100)) AS faves, 1 AS lvl
FROM dbo.favorite
SELECT name, CAST(f.favorite + ',' + b.faves AS NVARCHAR(100)) AS faves, b.lvl + 1 AS lvl
FROM dbo.favorite f JOIN blah b ON = b.myname
WHERE CHARINDEX(f.favorite,b.faves) = 0
SELECT x.myname,x.faves FROM (
SELECT blah.myname,blah.faves,ROW_NUMBER() OVER (PARTITION BY MAX(blah.lvl) ORDER BY blah.faves ) as rowno
FROM blah JOIN (SELECT myname, MAX(lvl) AS lvl FROM blah GROUP BY myname) blah2 ON blah.lvl = blah2.lvl AND blah.myname = blah2.myname
group BY blah.myname,blah.faves
) x
WHERE x.rowno = 1
GROUP BY x.myname,x.faves

The resulting value for this statements is:

B     apple,banana
A     apple,banana,guava,pear


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: