August 5, 2008 at 10:11 am
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 thie above. I dont think if I can use the pivot Function..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
[/color]
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
August 5, 2008 at 10:39 am
Hi there,
Ok I think a recursive CTE might get you what you want.
DECLARE @Yourtbl TABLE
(PK INT,
Restaurant VARCHAR(100),
RoomStatus VARCHAR(100))
INSERT INTO @Yourtbl
SELECT 10,'Yonos Restaurant','Room A- Incomplete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room B- complete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room B- complete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room D- Incomplete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 101- complete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 102- Incomplete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 103- Incomplete Certification '
--I do this so that I can get a ROW_NUMBER
--****************************************
DECLARE @tbl TABLE
(ID INT IDENTITY(1,1),
PK INT,
Restaurant VARCHAR(100),
RoomStatus VARCHAR(100))
INSERT INTO @tbl
(PK,Restaurant,RoomStatus)
SELECT
PK,Restaurant,RoomStatus
FROM @Yourtbl
--****************************************
;WITH myCTE (ID,PK,Restaurant,RoomStatus)
AS
(
SELECT ID,
PK,
Restaurant,
CAST(RoomStatus as VARCHAR(1000))as RoomStatus
FROM @tbl a
UNION ALL
SELECT
a.ID,
a.PK,
a.Restaurant,
CAST(a.RoomStatus + b.RoomStatus as VARCHAR(1000)) as RoomStatus
FROM @tbl a
INNER JOIN myCTE b ON A.ID > B.Id AND a.Restaurant = b.Restaurant
)
SELECT a.*
FROM myCTE a
INNER JOIN (SELECT MAX(LEN(RoomStatus)) as [Cnt],Restaurant FROM myCTE GROUP BY Restaurant) b
ON len(a.RoomStatus) = b.cnt AND a.Restaurant = b.Restaurant
ORDER BY Restaurant DESC
Let me know if you have any problems 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 10:47 am
Chris,
Really apperciate your help, Sorry forgot to mention Since I will have close to 500 Restaurants, I will not be able to hardcode each value. Would you know of any other way.
Thanks
Max
August 5, 2008 at 10:56 am
I'm not sure what you mean about Hardcoding the names?
None of my code should be hard coded, the only section that was hard coded was the creating of the data which I assume you have a perm table for?
If I have harded coded please quote the code in a reply 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 11:19 am
Sorry about that Chris, I plugged in my query where you were inserting by selecting UNION ALL, and it seems to work find when i execute it with some keys in the where clause but seems to get lost when executing thru the whole table since I have over 500 rows in the table.
so this is how the query looks like....Thanks
DECLARE @Yourtbl TABLE
(PK INT,
Restaurant VARCHAR(100),
RoomStatus VARCHAR(max))
INSERT INTO @Yourtbl
select
d.intvenue_fk as PK,
v.nvcVenue_Name as Restaurent,
nvcDining_Name+' - '+cs.nvcCertificationStatus+'; ' as RoomStatus
FROM [VenuDB].[dbo].[tblVenueDiningInformation] d
inner join [VenuDB].[dbo].[tblVenueInformation] v on v.intVenue_pk=d.intVenue_fk
inner join [VenuDB].[dbo].tblVenueCertificationStatus cs on cs.intCertificationStatus_pk=d.intCertificationStatus_fk
--where intvenue_fk in (10,25)
--I do this so that I can get a ROW_NUMBER
--****************************************
DECLARE @tbl TABLE
(ID INT IDENTITY(1,1),
PK INT,
Restaurant VARCHAR(100),
RoomStatus VARCHAR(max))
INSERT INTO @tbl
(PK,Restaurant,RoomStatus)
SELECT
PK,Restaurant,RoomStatus
FROM @Yourtbl
--****************************************
;WITH myCTE (ID,PK,Restaurant,RoomStatus)
AS
(
SELECT ID,
PK,
Restaurant,
CAST(RoomStatus as VARCHAR(1000))as RoomStatus
FROM @tbl a
UNION ALL
SELECT
a.ID,
a.PK,
a.Restaurant,
CAST(a.RoomStatus + b.RoomStatus as VARCHAR(1000)) as RoomStatus
FROM @tbl a
INNER JOIN myCTE b ON A.ID > B.Id AND a.Restaurant = b.Restaurant
)
SELECT a.*
FROM myCTE a
INNER JOIN (SELECT MAX(LEN(RoomStatus)) as [Cnt],Restaurant FROM myCTE GROUP BY Restaurant) b
ON len(a.RoomStatus) = b.cnt AND a.Restaurant = b.Restaurant
ORDER BY Restaurant DESC
August 5, 2008 at 11:24 am
Hi 🙂
When you say it gets lost?!!? does it throw an Error?
could you let me know what you mean, and send through an example as well .
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 11:28 am
The query still was being executed over 5 minutes gone by without an error so I stopped it.
August 5, 2008 at 11:32 am
mmmm ooops,
I'll work on the performance and try and get back to you 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 6, 2008 at 4:36 am
HI there,
Does anyone have any ideas or alternative methods?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 6, 2008 at 5:24 am
Ok I think I have a better way of doing this.
I'm using a dynamic pivout query so I hope this helps
DROP TABLE #Yourtbl
CREATE TABLE #Yourtbl
(PK INT,
Restaurant VARCHAR(100),
RoomStatus VARCHAR(100))
INSERT INTO #Yourtbl
SELECT 10,'Yonos Restaurant','Room A- Incomplete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room B- complete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room B- complete Certification' UNION ALL
SELECT 10,'Yonos Restaurant','Room D- Incomplete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 101- complete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 102- Incomplete Certification' UNION ALL
SELECT 25,'Atlanta Fish Market','Room 103- Incomplete Certification '
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Colslist2 VARCHAR(MAX)
DROP TABLE #Cols
CREATE TABLE #Cols (ID INT IDENTITY(1,1),Head VARCHAR(MAX))
INSERT #Cols (Head)
SELECT DISTINCT RoomStatus
FROM #Yourtbl
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + CAST(ID as VARCHAR) + ']'
FROM #Cols t
SELECT @ColsList2 = COALESCE(@ColsList2 + '+ISNULL([', '+ISNULL([') + CAST(ID as VARCHAR) + '],'''')'
FROM #Cols t
EXEC ('SELECT PK,Restaurant,'+@ColsList2+' as [Result]
FROM
(
SELECT
CAST(ID as VARCHAR(max)) as [ID],
PK,
Restaurant,
Head
FROM #Yourtbl a
INNER JOIN #Cols b ON a.Roomstatus = b.HEad
) t
PIVOT (MIN(Head) FOR ID IN (' + @ColsList + ')) PVT')
Let me know if you have issues 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 6, 2008 at 7:21 am
Chris,
You are awesome, This works perfect, only thing I had to change was to set the RoomStatus varchar to (max) since some of the Restaurants had like many many rooms. I had spent a few nights trying to make it work with Pivot, cte or cross apply, nothing seemed to work.
I really apperciate you getting back to me and taking care of this. Let me know if I can be any help with any issues.
Thanks
Max
August 6, 2008 at 7:54 am
Hi there,
No problem what so ever, and just so you know I tried this with a list of about 3000 and it still worked pretty fast.
The only thing I don't like is that it uses dynamic SQL :w00t:
It was actually a pleasure helping as I figured out something I had never done before 🙂
thanks for the challenge he he he
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply