Select within select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select within select

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2014-06-05 : 13:17:03

Hi,

Suppose I have two rows in my table like this:

NAME NUMBER CASE
SMITH 10 3
SMITH 10 4
JONES 12 1
BROWN 13 2
BROWN 13 5

I need to combine the results into one row showing the NAME and NUMBER once, but combining the CASE separated by a comma, like...

NAME NUMBER CASE
SMITH 10 3, 4
JONES 12 1
BROWN 13 2, 5

How might I accomplish this? Thanks for any help you could provide.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-06-06 : 00:14:23

Search this forum for concatenation. I believe the post was by swepeso. It has almost this exact request documented. I'd do it for u, but on my phone and don't have a computer.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-06 : 01:25:07

Try this..


CREATE TABLE #Tset(NAME varchar(MAX),NUMBER int,[CASE] int)
INSERT INTO #Tset VALUES('SMITH',10,3),('SMITH',10,4),('JONES',12,1),('BROWN',13,2),('BROWN',13,5)
SELECT DISTINCT NAME,NUMBER,STUFF((SELECT ','+CAST([CASE] AS varchar(max)) FROM #Tset T WHERE T.NUMBER = T1.NUMBER AND T.NAME = T1.NAME FOR XML PATH('')),1,1,'') FROM #Tset T1 
DROP TABLE #Tset

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources