CODEWITHSUNDEEP
sql-server
AliceSmith
AliceSmith

Reputation: 371

SQL Pivot(?) - Flatten By-Day/Event select to contain columns for a complete week

I'm hoping someone might be able to assist me in figuring out how to flatten a select using the table shown so that I can select a single row per User, containing the columns for each event.

In short, I'm creating the following temp table, and it is filled with events to be attended by select users on a given week.

CREATE TABLE #ByEvent
       (WeekBegin        SMALLDATETIME      NOT NULL,
        UserNo           INT                NOT NULL,
        Name             VARCHAR(30)        NOT NULL,
        Attend           BIT                NOT NULL,               
        --- Event Portion ---------------------------   
        EventID          INT                NOT NULL,
        EventDate        SMALLDATETIME      NOT NULL,
        Hrs              NUMERIC(4,2)       NOT NULL,
        EventScheduleBeg SMALLDATETIME      NOT NULL,
        EventScheduleEnd SMALLDATETIME      NOT NULL,               
        Val1             VARCHAR(1)         NOT NULL,
        Val2             VARCHAR(1)         NOT NULL)

Please see the sample table contents directly below. I have reduced some values to their relevant pieces for clarity:

WeekBegin   UserNo  Name   Attend EventID EventDate  Hrs  ScheduleBeg  ScheduleEnd
2018-05-27  24      Jim    T      257     5/29/18    4.0  12:00        16:00  
2018-05-27  24      Jim    T      258     5/29/18    2.0  18:00        20:00
2018-05-27  24      Jim    T      259     5/30/18    2.5  12:00        14:30   
2018-05-27  24      Tim    F      259     5/30/18    2.5  12:00        14:30

What I need is to output the data as shown in the select below. However, I'm having trouble understanding how to accomplish this. I believe I need a pivot (if I am on the wrong track and over complicating, please let me know), but there may be none or 5+ events in a given week, and because of this I don't know in advance how many columns I need in the select statement. Hopefully this makes sense. If anyone point me in the right direction, I would really appreciate it!

SELECT WeekBegin
      ,UserNo
      ,Name
      ,Attend
      ,FirstEventID
      ,FirstEventDate
      ,FirstEventHrs
      ,FirstEventScheduleBeg
      ,FirstEventScheduleEnd
      ,FirstVal1
      ,FirstVal2
      ,SecondEventID
      ,SecondEventDate
      ,SecondEventHrs
      ....

Upvotes: 0

Views: 50

Answers (1)

shawnt00
shawnt00

Reputation: 17915

This will get you started but you really ought to add more detail to the question. For one it appears that there isn't a hard limit on the number of events and that you would probably prefer to only have as many columns as necessary. A dynamic SQL solution would be able to accomplish that though if you really need the columns to be prefixed with "First", "Second", "Third" and so on you have another small hurdle to overcome with that requirement.

By the way, there are many ways to group by week and hard-coding a random date in May is probably not the method you'd actually use in production code.

;with data as (
    select *,
        datediff(week, cast('20180527' as date)) as WeekNo,
        row_number() over (
            partition by UserNo order by EventDate, ScheduleBeg) as RN
    from #ByEvent
    where EventDate >= cast('20180527' as date)
)
select WeekNo, UserNo, ...
    case when RN = 1 then EventID end as FirstEventID, ...
    case when RN = 2 then EventID end as SecondEventID, ...
from data
group by WeekNo, UserNo;

Upvotes: 1

Related Questions