T-SQL Pivot multiple columns duplicates results

Question

I have a table with aggregated data that I would like to pivot. Here is a sample of the data:

Unit#   EffectiveDay    RequestCount    ResponseCount
5       1               0               0
5       2               8               8
5       3               4               4
5       4               4               4
5       5               2               2
5       6               0               0
5       7               2               2
6       1               0               0
6       2               0               0
6       3               0               0
6       4               0               0
6       5               0               0
6       6               0               0
6       7               0               0

I can successfully pivot the RequestCount column, but when I add the ResponseCount column it's as if the rows increased exponentially. Instead of two rows (one for each unit#), I get 14.

select  *
from    (
            select  Unit#
                    ,concat('Request', EffectiveDay) AS RequestDay
                    ,RequestCount
                    ,concat('Response', EffectiveDay) As ResponseDay
                    ,ResponseCount
            from    #tmpLogPayConnexion
        ) as P
pivot   (
            sum(RequestCount)
            for RequestDay in ([Request1], [Request2], [Request3], [Request4], [Request5], [Request6], [Request7])
        ) as pvtRequest
pivot   (
            sum(ResponseCount)
            for ResponseDay in ([Response1], [Response2], [Response3], [Response4], [Response5], [Response6], [Response7])
        ) as pvtResponse

I know this is possible, but I am stumped.

Thank you.

Sean Lange · Accepted Answer · 2018-03-08 21:27:34Z

1

This seems like a time for a crosstab, or conditional aggregation.

select [Unit#]
    , Request1 = max(case when EffectiveDay = 1 then RequestCount end)
    , Request2 = max(case when EffectiveDay = 2 then RequestCount end)
    , Request3 = max(case when EffectiveDay = 3 then RequestCount end)
    , Request4 = max(case when EffectiveDay = 4 then RequestCount end)
    , Request5 = max(case when EffectiveDay = 5 then RequestCount end)
    , Request6 = max(case when EffectiveDay = 6 then RequestCount end)
    , Request7 = max(case when EffectiveDay = 7 then RequestCount end)
    , Response1 = max(case when EffectiveDay = 1 then ResponseCount end)
    , Response2 = max(case when EffectiveDay = 2 then ResponseCount end)
    , Response3 = max(case when EffectiveDay = 3 then ResponseCount end)
    , Response4 = max(case when EffectiveDay = 4 then ResponseCount end)
    , Response5 = max(case when EffectiveDay = 5 then ResponseCount end)
    , Response6 = max(case when EffectiveDay = 6 then ResponseCount end)
    , Response7 = max(case when EffectiveDay = 7 then ResponseCount end)
from #tmpLogPayConnexion
group by [Unit#]
order by [Unit#]

answered Mar 8, 2018 at 21:27

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2122092 Over a year ago

Sean, this definitely works and gets me what I need. In your opinion, a pivot is not possible (just trying to learn)?

Sean Lange Over a year ago

I prefer to use a crosstab when I can instead of a pivot. The syntax is far less obtuse for me and they are a bit more flexible. One real challenge with a pivot is multiple sets like you are trying to do. It isn't as true as it used to be but the crosstab has a very slight performance bonus over pivot also. But that doesn't really come into play unless you are talking a million rows or more.

Collectives™ on Stack Overflow

T-SQL Pivot multiple columns duplicates results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related