CODEWITHSUNDEEP
sql-serversyntax-errorpivot
Arthur Rey
Arthur Rey

Reputation: 3058

Performing operations in a Pivot

I am trying to implement a PIVOT but I am having trouble performing basic operations in it.

Current table:

week_no  username  days      pick_count duration
------------------------------------------------
Week 50  Beck W    Wednesday 227        7978
Week 50  Beck W    Friday    320        7481
Week 50  Beck W    Friday    282        5718
Week 50  Cockram D Thursday  165        10478
Week 50  Cowell P  Thursday  145        14403
Week 50  Cowell P  Thursday  159        7450
Week 50  Cowell P  Friday    217        13101
...

Expected result:

week_no username  monday tuesday wednesday thursday friday saturday sunday
--------------------------------------------------------------------------
Week 50 Beck W    NULL   NULL    102       NULL     164    NULL     NULL
Week 50 Cockram D NULL   NULL    NULL      56       NULL   NULL     NULL
Week 50 Cowell P  NULL   NULL    NULL      50       59     NULL     NULL
...

The expected result should be calculated as followed: pick_count * 3600 / duration. That is the calculation I am having problem computing. When PIVOTing like so

SELECT 
    *
FROM 
(
    SELECT 
        week_no,
        username,
        days,
        pick_count,
        duration
    FROM
        table
) AS src
PIVOT 
(
    SUM(pick_count) * 3600 / SUM(duration) FOR days IN (monday, tuesday, wednesday, thursday, friday, saturday, sunday)
) AS pvt

I get Incorrect syntax near '*'.

Upvotes: 2

Views: 48

Answers (2)

Arthur Rey
Arthur Rey

Reputation: 3058

I eventually found what I was looking for. Indeed, all I needed to do was to compute the calculation in the SELECT instead of the PIVOT.

SELECT 
    *
FROM 
(
    SELECT 
        week_no,
        username,
        days,
        SUM(pick_count) * 3600 / SUM(duration) AS pick_rate
    FROM
        table
    GROUP BY
        week_no,
        username,
        days
) AS src
PIVOT 
(
    MAX(pick_rate) FOR days IN (monday, tuesday, wednesday, thursday, friday, saturday, sunday)
) AS pvt

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453395

PIVOT is not very flexible.

You can just use the old style cross tab approach.

SELECT week_no,
       username,
       SUM(CASE WHEN days = 'monday' then pick_count end) * 3600 
                    / SUM(CASE WHEN days = 'monday' then duration end) as monday,
       SUM(CASE WHEN days = 'tuesday' then pick_count end) * 3600 
                    / SUM(CASE WHEN days = 'tuesday' then duration end) as tuesday
/*TODO: Add other five days*/
FROM   YourTable
GROUP  BY week_no,
          username

Upvotes: 2

Related Questions