Create Pivot table in SQL without Pivot Function

Question

All,

I have the following table.

SalesDate   SubChannel      Country  NetQuantity
20140826    TV Attributable      CA          194
20140826    Unknown              CA           60
20140826    Web Property         CA           64
20140826    Overall              CA          264
20140826    Search               CA           70

I want to display this information as a pivot table.

Before I was using the following piece of code to make this happen, but the current system I have to migrate to, doesn't support pivot functions.

Select      SalesDate,
    Country,
    [Search],
    [Unknown],
    [Web Property],
    [TV Attributable],
    [Overall]
From temp
PIVOT
(
Sum([NetQuantity])
FOR [SubChannel] IN ([Search],[Unknown],[Web Property],[TV Attributable],[Overall])
) as p

Can somebody help me accomplish the following output:

SalesDate   Country Search  Unknown  WebProperty       TVAttributable      Overall
20140826         CA     70       60           64                  194          264

What system are you using? There might be some silimar function if pivot isn't supported. — jpw
– jpw, Commented Sep 19, 2014 at 17:00
I search all the documentation and even when the system uses psql, it doesn't support pivot functions. So I am just trying to find a work around. But so far I haven't been able to come up with a query that could do the trick. — Pierre
– Pierre, Commented Sep 19, 2014 at 17:03
You tagged the post with psql, is it Postgresql you are using? — jpw
– jpw, Commented Sep 19, 2014 at 17:03

Joachim Isaksson · Accepted Answer · 2014-09-19 17:14:05Z

You can use CASE statements with a GROUP BY to do the pivot "manually";

SELECT "SalesDate", MAX("Country") "Country",
  MAX(CASE WHEN "SubChannel"='Search' 
           THEN "NetQuantity" END) "Search",
  MAX(CASE WHEN "SubChannel"='Unknown' 
           THEN "NetQuantity" END) "Unknown",
  MAX(CASE WHEN "SubChannel"='Web Property' 
           THEN "NetQuantity" END) "WebProperty",
  MAX(CASE WHEN "SubChannel"='TV Attributable' 
           THEN "NetQuantity" END) "TVAttributable",
  MAX(CASE WHEN "SubChannel"='Overall' 
           THEN "NetQuantity" END) "Overall"
FROM temp
GROUP BY "SalesDate";

An SQLfiddle to test with.

Basically, the CASE statements pick out a value if the subchannel matches or null if no match. The MAX is then used to pick the greatest (non null) match for each subchannel.

If the sales dates need to be split by country, you can just replace the MAX("Country") expression with just "Country" and add Country to the GROUP BY.

jpw · Accepted Answer · 2014-09-19 17:11:59Z

0

You can use CASE to determine what to sum up for every category. As you use SUM in the example pivot, I think you might want this and not MAX:

Select
    "SalesDate",
    "Country",
    sum(case when "SubChannel" = 'Search' then "NetQuantity" else 0 end) "Search",
    sum(case when "SubChannel" = 'Unknown' then "NetQuantity" else 0 end) "Unknown",
    sum(case when "SubChannel" = 'Web Property' then "NetQuantity" else 0 end) "Web Property",
    sum(case when "SubChannel" = 'TV Attributable' then "NetQuantity" else 0 end) "TV Attributable",
    sum(case when "SubChannel" = 'Overall' then "NetQuantity" else 0 end) "Overall"
from temp
group by "SalesDate", "Country"

Sample SQL Fiddle

answered Sep 19, 2014 at 17:11

jpw

45k6 gold badges71 silver badges90 bronze badges

1 Comment

jpw Over a year ago

@Pierre I thought so.

Collectives™ on Stack Overflow

Create Pivot table in SQL without Pivot Function

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related