How to append a multiple rows value into single.

Question

How to append a multiple rows value into single.

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
September 4, 2010 at 10:22 pm

#228664

Hi I have a table name "employee"
it has the values.
EmployeeName Id HelpdeskTicketNo.
Sekar 64867 456
Sekar 64867 459
Sekar 64867 460
kumar 68567 215
kumar 68567 216
etc..............
Now i need this kind of out put.
EmployeeName Id HelpdeskTicketNo.
Sekar 64867 456,459,460...........
kumar 68567 215,216

Viewing 9 posts - 1 through 8 (of 8 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Chandrasekar, you are here for a long time ; why dint you learn to provide your question so effectively so to extract best help from the volunteers?

Please provide your question as per standard.

Now, for the query, here it is, to append rows into one row based on a column.

DECLARE @tab TABLE

(

EmployeeName VARCHAR(100),

Id INTEGER ,

HelpdeskTicketNo INTEGER

)

INSERT @tab

SELECT 'Sekar' ,64867, 456

UNION ALL SELECT 'Sekar' ,64867, 459

UNION ALL SELECT 'Sekar' ,64867, 460

UNION ALL SELECT 'kumar' ,68567, 215

UNION ALL SELECT 'kumar' ,68567, 216

SELECT p1.EmployeeName , p1.id,

STUFF ( ( SELECT ','+ cast( HelpdeskTicketNo AS VARCHAR)

FROM @tab p2

WHERE p2.id = p1.id

ORDER BY EmployeeName

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Ticket_Nos

FROM @tab p1

GROUP BY p1.EmployeeName, p1.id

ORDER BY ID;

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

If you want to understand what the code does, read thro this:

http://www.sqlservercentral.com/Forums/FindPost962505.aspx

Wayne Sheffield did an extraordinary job in explaining the code! 🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

And also dont forget to read skcadvre's post on the same thread, excellent explanations on FOR XML path.. thanks Wayne and skcadavre..

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 4

chandrasekaran.ganapathy

SSCommitted

Points: 1738

September 5, 2010 at 3:18 am

#1216641

thanks a lot.

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 5

.value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values

can you explain this part only?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

.value(varchar) handles the special characters like < or > or & etc.. please go thro this for further details:

http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx%5B/url%5D

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

Test it

select stuff('test',1,1,SPACE(0))

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Here's a nice APPLY version:

SELECT d.EmployeeName,

d.Id,

TicketNumbers = STUFF(iTVF.TicketNumbers, 1, 1, SPACE(0))

FROM @tab d

CROSS APPLY

(SELECT ',' + CAST(HelpdeskTicketNo AS VARCHAR)

FROM @tab

WHERE id = d.id

ORDER BY HelpdeskTicketNo

FOR XML PATH('')) iTVF (TicketNumbers)

GROUP BY d.Id, d.EmployeeName, iTVF.TicketNumbers

ORDER BY d.Id

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden