Tsql grouping question

Question

Post reply

Tsql grouping question

work.dev

SSC Veteran

Points: 204
More actions
February 27, 2012 at 8:51 am

#257492

Hello,
I have data like below
create table #Temp( ID varchar(10), Name varchar(100) )
insert into #Temp values('1','Brian')
insert into #Temp values('1','James')
insert into #Temp values('2','Jim')
insert into #Temp values('2','John')
insert into #Temp values('2','Bob')
I need write a query so that the output looks like below
1 Brain,James
2 Tim,John,Bob
I figured i can use COALESCE to get a comma separated list of names.but am struggling to group the
names by Id. Any help is greatly appreciated.
declare @TempMessage as varchar(100)
SET @TempMessage =NULL
select @TempMessage = COALESCE(@TempMessage + ',' + Name,Name)
from #Temp

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

~~Can you explain where this result comes from?~~

This was edited for clarity while I was posting. 😀

Btw, excellent job posting you ddl and sample data!!!

What you are describing is a cross tab, a dynamic one to be more specific. If at all possible this is a lot easier in the front end. If you need to do this in sql Jeff wrote a couple of article about this.

http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

It's best to let your presentation layer handle things like this. If you have to do it in the database layer, you can use FOR XML to concatenate the results. Just search for "concatenate with FOR XML" and you should find what you require.

John

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 3

I have used for xml path to do a similar thing before.

;with SampleDataR as

(

select *, ROW_NUMBER() over (partition by ID order by Name) rownum

from #Temp

)

select distinct ID,(

select Name

+ case when s1.rownum = (select MAX(rownum) from SampleDataR where ID = s1.ID)

then '' else ',' end from SampleDataR s1

where s1.ID = s2.ID

for xml path(''),type).value('(.)[1]','varchar(max)') CSVNames

from SampleDataR s2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

/facepalm

Thanks guys don't know why I didn't think of for xml. :hehe:

Guess I need more coffee this morning.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

work.dev SSC Veteran Points: 204 More actions · Answer 5

work.dev

SSC Veteran

Points: 204

February 27, 2012 at 9:23 am

#1452147

Thanks for the reply Guyz. you are the best 🙂

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

John Mitchell-245523 (2/27/2012)
It's best to let your presentation layer handle things like this.

I'll say "It Depends"... especially if there's no presentation layer that can handle it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)