CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
wh4tshisf4c3
wh4tshisf4c3

Reputation: 133

SQL Dynamic Pivoting a "Week" Table

Table design:

| PeriodStart | Person | Day1 | Day2 | Day3 | Day4 | Day5 | Day6 | Day7 |
-------------------------------------------------------------------------
| 01/01/2018  |  123   |  2   |  4   |  6   |  8   |  10  |  12  |  14  |
| 01/15/2018  |  246   |  1   |  3   |  5   |  7   |  9   |  11  |  13  |

I am trying to create a pivot statement that can dynamically transpose both rows.

Desired output:

|    Date    | Person | Values |
--------------------------------
| 01/01/2018 |   123  |    2   |
| 01/02/2018 |   123  |    4   |
| 01/03/2018 |   123  |    6   |
| 01/04/2018 |   123  |    8   |
| 01/05/2018 |   123  |   10   |
| 01/06/2018 |   123  |   12   |
| 01/15/2018 |   246  |    1   |
| 01/16/2018 |   246  |    3   |
| 01/17/2018 |   246  |    5   |
 ... and so on. Date order not important

The following query will help initialize things:

DECLARE @WeekTable TABLE (
    [PeriodStart] datetime
    , [Person] int
    , [Day1] int
    , [Day2] int
    , [Day3] int
    , [Day4] int
    , [Day5] int
    , [Day6] int
    , [Day7] int
)

INSERT INTO @WeekTable(
    [PeriodStart],[Person],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7]
)
VALUES ('01/01/2018','123','2','4','6','8','10','12','14')
    ,('01/15/2018','246','1','3','5','7','9','11','13')

Upvotes: 3

Views: 466

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other option is to do that with APPLY operator 

SELECT DATEADD(DAY, Days-1, Dates) Date, a.Person, Value
FROM @WeekTable t CROSS APPLY (
    VALUES (PeriodStart, Person, 1, Day1), (PeriodStart, Person, 2, Day2),
           ..., (PeriodStart, Person, 7, Day7)
)a(Dates, Person, Days, Value)

Upvotes: 3

gordy
gordy

Reputation: 9786

you can use unpivot to turn the day columns back into rows and then parse the number out of the column name:

with periods as (
  select * from @WeekTable
  unpivot ([Values] for [Day] in (Day1, Day2, Day3, Day4, Day5, Day6, Day7))x
)

select dateadd(day, convert(int, substring(Day, 4, 1)), PeriodStart), Person, [Values]
from periods

fiddle

Upvotes: 2

Related Questions