Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server query problem Expand / Collapse
Author
Message
Posted Saturday, May 4, 2013 5:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
hi friends i have small doubt in sql server plz tell me how to solve


i have 2 tables and data contains like

aa
id , color , colorid
1 , red , 10
2 , white , 20

and 2nd table contains like
bb
colorid , name
10 , carrote
10 , tammoto
20 , sugar
20 , milk

based on this table i want output like below

color name
red carrote,tammoto
white sugar ,milk

actualy i follow query inner join concept .but iam not get exactely this output.

plese tell me how to write query to solve this issue in sql server.
Post #1449412
Posted Saturday, May 4, 2013 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:48 AM
Points: 56, Visits: 533
Please check this code:
select 
a.*,
STUFF((SELECT ', '+name FROM bb WHERE colorid = a.colorid FOR XML PATH('')) , 1 , 1 , '' )
from aa a

Post #1449418
Posted Saturday, May 4, 2013 1:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
salliven (5/4/2013)
Please check this code:
select 
a.*,
STUFF((SELECT ', '+name FROM bb WHERE colorid = a.colorid FOR XML PATH('')) , 1 , 1 , '' )
from aa a



If you change the second "1" to a 2, it'll get rid of the leading space.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449459
Posted Sunday, May 5, 2013 12:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:48 AM
Points: 56, Visits: 533
Oh, yes. Thanks Jeff!
Post #1449499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse