Help need with Group by or (with one to many)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help need with Group by or (with one to many)

Author

Topic

maximation
Starting Member

6 Posts

Posted - 2008-08-05 : 11:41:54

I need to group the records by Restaurant and RoomStatus within one column per Restaurant-- My Desire output need to look like this

10 Yonos Restaurant Room A- Incomplete Certification ; Room B- complete Certification Room C- complete Certification
25 Atlanta Fish Market Room 101- complete Certification; Room 102- Incomplete Certification

Is there a quick way to achieve this any help would be highly appreciated. my query currently looks like this with the output as follows

select
d.intvenue_fk as PK,
v.nvcVenue_Name as Restaurant,
nvcDining_Name+'- '+cs.nvcCertificationStatus as RoomStatus
FROM tblDining d
inner join tblVenue v on v.intVenue_pk=d.intVenue_fk
inner join tblCertStatus c on c.intCertificationStatus_pk=d.intCertificationStatus_fk

--PK-- --Restaurant-- --RoomStatus--
10 Yonos Restaurant Room A- Incomplete Certification
10 Yonos Restaurant Room B- complete Certification
10 Yonos Restaurant Room C- complete Certification
25 Atlanta Fish Market Room 101- complete Certification
25 Atlanta Fish Market Room 102- Incomplete Certification

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 11:52:18

I'm not following, can yo please clarify more? What is the current output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:08:19

[code]SELECT m.PK,m.Restaurant,
LEFT(rl.roomlist,LEN(rl.roomlist)-1) AS RoomStatusList
FROM (SELECT DISTINCT PK,Restaurant
FROM YourTable)m
CROSS APPLY (SELECT RoomStatus +';' AS [text()]
FROM YourTable
WHERE PK=m.PK
AND Restaurant=m.Restaurant
FOR XML PATH(''))rl(roomlist)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources