query :

Question

query :

ankushchawla03

Newbie

Points: 9
More actions
November 17, 2008 at 5:54 am

#196147

I have a table or you can say a result of query is like that
A B
_______
a x1
a x2
a x3
b y1
b y2
I need a result like this
A B
__________
a x1,x2,x3
b y1,y2,y3
I have tried my best but unable to generate such output .
Kindly help
Regards
Ankush

Viewing 3 posts - 1 through 3 (of 3 total)

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

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

ankushchawla03 (11/17/2008)
I have a table or you can say a result of query is like that
A B
_______
a x1
a x2
a x3
b y1
b y2
I need a result like this
A B
__________
a x1,x2,x3
b y1,y2,y3
I have tried my best but unable to generate such output .
Kindly help
Regards
Ankush

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Garadin One Orange Chip Points: 29613 More actions · Answer 2

[font="Courier New"]

--------------- CREATE SAMPLE DATA TABLE ------------------

DECLARE @a TABLE(

A VARCHAR(5),

B VARCHAR(5))

INSERT INTO @a(A,B)

SELECT 'a','x1' UNION ALL

SELECT 'a','x2' UNION ALL

SELECT 'a','x3' UNION ALL

SELECT 'b','y1' UNION ALL

SELECT 'b','y2'

-----------------------------------------------------------

------------- CTE to concatenate B ------------------------

;WITH ConcatB(A,CB) AS(

SELECT A1.A,STUFF((SELECT

', ' + B

FROM

@A A2

WHERE A2.A = A1.A

FOR XML PATH ('')), 1,2,'')

FROM @a A1

GROUP BY A1.a)

-----------------------------------------------------------

------------ SELECT ---------------------------------------

SELECT DISTINCT A.A, CB

FROM @a A

INNER JOIN ConcatB ON A.A = ConcatB.A

-----------------------------------------------------------[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]