July 29, 2012 at 10:28 pm
Hi,
I'm trying to merge rows and columns into a single field based upon the weekday. Please refer the attached image and table structure (with data).
For example, in the provided data Wednesday has 4 rows of data. How to merge them to a single field ?
So that for each day i can have one row and one column of data.
Is it possible to do ? Can any one help me on this.
I've seen a similar case long back for mathematical data i.e summing up item wise data for each sales guy.
Thanks
July 30, 2012 at 12:33 am
Is it possible to give the desired output for the sample data that you provided?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2012 at 12:42 am
@ Koen Verbeeck - The output will display the details so that each weekday has a column.
Below is the sql i've managed to come out with.
select Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday from (
SELECT
--WeekDay,
Cycle ,ShowID, TheatreID, ShowDetails, ChiefGuest, ShowStartTime, ShowEndTime, CoOrdinator, Remarks,
Sunday = (
case
When
WeekDay = 'Sunday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Monday = (
case
When
WeekDay = 'Monday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Tuesday = (
case
When
WeekDay = 'Tuesday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Wednesday = (
case
When
WeekDay = 'Wednesday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Thursday = (
case
When WeekDay = 'Thursday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Friday = (
case
When
WeekDay = 'Friday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END),
Saturday = (
case
When
WeekDay = 'Saturday' THEN ShowID + char(13) + TheatreID + char(13) + ShowDetails + char(13) + ChiefGuest + char(13) + ShowStartTime + char(13) + ShowEndTime + char(13) + CoOrdinator + char(13) + Remarks END)
FROM
VIPShows
--Group By Cycle,ShowID, TheatreID, WeekDay, ShowDetails, ChiefGuest, ShowStartTime, ShowEndTime, CoOrdinator, Remarks
--ORDER BY Cycle,ShowID, TheatreID, ShowDetails, ChiefGuest, ShowStartTime, ShowEndTime, CoOrdinator, Remarks
)sx
Is there a more efficient way of doing this ?
Thanks
July 30, 2012 at 12:54 am
At first sight I would look into the UNPIVOT operator:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply