Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, April 22, 2016 3:21 AM
Points: 216, Visits: 4,717
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: Monday, June 20, 2016 8:25 AM
Points: 60, Visits: 592
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 40,752, Visits: 37,998
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."

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: Monday, June 20, 2016 8:25 AM
Points: 60, Visits: 592
Oh, yes. Thanks Jeff!
Post #1449499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse