CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Daniel Minnaar
Daniel Minnaar

Reputation: 6295

How do I use SQL PIVOT for multiple aggregates?

I have a source table that looks like the below:

╔════════════════════╦════════════════╦══════════════╦══════════════════╗
║       Topic        ║     Person     ║ PersonsReply ║  PersonsComment  ║
╠════════════════════╬════════════════╬══════════════╬══════════════════╣
║ Is the earth flat? ║ This Person    ║ Yes          ║ It's flat.       ║
║ Is the earth flat? ║ That Person    ║ No           ║ It's round       ║
║ Is the earth flat? ║ Another Person ║ Maybe        ║ Don't care.      ║
╚════════════════════╩════════════════╩══════════════╩══════════════════╝

But from the examples I've seen online, I can't seem to transform my data into a tabular representation like below:

╔════════════════════╦══════════════════╦══════════════════╦═════════════════════╦════════════════════╦════════════════════╦═══════════════════════╗
║       Topic        ║ ThisPersonsReply ║ ThatPersonsReply ║ AnotherPersonsReply ║ ThisPersonsComment ║ ThatPersonsComment ║ AnotherPersonsComment ║
╠════════════════════╬══════════════════╬══════════════════╬═════════════════════╬════════════════════╬════════════════════╬═══════════════════════╣
║ Is the earth flat? ║ Yes              ║ No               ║ Maybe               ║ It's flat          ║ It's round         ║ Don't care            ║
╚════════════════════╩══════════════════╩══════════════════╩═════════════════════╩════════════════════╩════════════════════╩═══════════════════════╝

How can I use SQL's PIVOT function for what I'm trying to achieve? Here is my sandbox: http://sqlfiddle.com/#!18/e198d/1

Right now I'm getting:

topic   ThisReply   ThatReply   AnotherReply
Is the earth flat?  (null)  (null)  (null)

Upvotes: 2

Views: 92

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

If you want to use a non-dynamic PIVOT, it becomes a small matter with a CROSS APPLY

Example

SELECT  *
FROM (
    SELECT topic
          ,B.*
    FROM ptest
    Cross Apply (values (person+'Reply',reply)
                       ,(person+'Comment',comment)

                )B(Item,Value)
) AS source
PIVOT ( max(value) FOR item IN ([ThisReply], [ThatReply], [AnotherReply],[ThisComment], [ThatComment], [AnotherComment]) ) AS pivotTable

Returns

topic               ThisReply   ThatReply   AnotherReply    ThisComment ThatComment AnotherComment
Is the earth flat?  Yes         No          Maybe           Its flat    Its round   Dont care

Upvotes: 2

GMB
GMB

Reputation: 222482

I would suggest using conditional aggregation to pivot your dataset:

  • this is a cross-database solution, so you just get to learn the technique once, and then you can use it anywhere

  • it usually performs at least as good as vendor-specific implementations

  • another advantage is that you can handle more complex aggregate expressions than when using pivot (which is not the case for your question, but might eventually come up)

Query:

select 
    topic,
    max(case when person = 'This person' then personsReply end) ThisPersonsReply,
    max(case when person = 'That person' then personsReply end) ThatPersonsReply,
    max(case when person = 'Another person' then personsReply end) AnotherPersonsReply,
    max(case when person = 'This person' then personsComment end) ThisPersonsComment,
    max(case when person = 'That person' then personsComment end) ThatPersonsComment,
    max(case when person = 'Another person' then personsComment end) AnotherPersonsComment
from ptest 
group by topic

Demo on DB Fiddle:

topic              | ThisPersonsReply | ThatPersonsReply | AnotherPersonsReply | ThisPersonsComment | ThatPersonsComment | AnotherPersonsComment
:----------------- | :--------------- | :--------------- | :------------------ | :----------------- | :----------------- | :--------------------
Is the earth flat? | Yes              | No               | Maybe               | Its flat           | Its round          | Dont care

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want conditional aggregation :

select topic,
       max(case when Person = 'This' then reply end) as ThisPersonsReply,
       max(case when Person = 'That' then reply  end) as ThatPersonsReply,
       max(case when Person = 'Another' then reply  end) as AnotherPersonsReply,
       max(case when Person = 'This' then comment end) as ThisPersonsComment,
       max(case when Person = 'That' then comment end) as ThatPersonsComment,
       max(case when Person = 'Another' then comment end) as AnotherPersonsComment
from ptest pt
group by topic;

Here is DB fiddle.

Upvotes: 2

Related Questions