Aggregate multiple values in a column into one column using SQL

Data:

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
UNION ALL
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 f.name = 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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: