T-SQL Help

Question

Post reply

T-SQL Help

Sql Student-446896

Default port

Points: 1425
More actions
April 7, 2008 at 1:28 pm

#184268

Hi my query returns the following result set.
Name Id Dept Code
------------------------------------------
Craig M. WhiteISNBF0113
Craig M. WhiteISNBF0545
Deryck ChaseISNBF0113
Diana LyonsISNBF0113
Diana LyonsISNBF0545
Hosey D Steve ISNBF 0113
Hosey D Steve ISNBF0545
I need to modify the query such that it returns the result like this. Basically combining the values of column "code" into a single group for one distinct record.
Name Id Dept Code
------------------------------------------
Craig M. WhiteISNBF0113, 0535
Deryck ChaseISNBF0113
Diana LyonsISNBF0113, 0535
Hosey D Steve ISNBF 0113 , 0535

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

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

Tony-257745 Ten Centuries Points: 1168 More actions · Answer 1

Could be done by using cursor, can it be done without cursor?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Uh, huh... several different ways in the following URL along with some performance pit-falls to avoid...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--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)

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

I've seen this type of solution in a few posts around here also, providing this is SQL 2005.

SELECT DISTINCT Name, ID, Dept,mylist

FROM myTable t1

CROSS APPLY

(SELECT CONVERT(VARCHAR(10),Code) + ',' AS [text()]

FROM myTable t2

WHERE t2.Name = t1.Name

FOR XML PATH('')) AS Dummy(myList)

N J Right there with Babe Points: 732 More actions · Answer 4

This would also work..

select distinct name , id ,dept ,code = replace(

(select code as [data()] from #test t

where t.name = t1.name

for xml PATH ('')

), ' ', ',')

from #test t1

NJ

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Ummm... be very careful using CROSS APPLY... it's just another way of making a "correlated subquery" which qualifies as RBAR and can be very slow.

--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)