SQL Moving a Row to be a column - PIVOT query

Question

Given:

+-----------+---------------+-------------+-------+
|   Name    |   Location    | Description | Value |
+-----------+---------------+-------------+-------+
| Company A | Houston       | Sales       |   100 |
| Company A | Houston       | Profit      |    50 |
| Company B | San Francisco | Sales       |   500 |
| Company B | San Francisco | Profit      |   200 |
| Company C | New York      | Sales       |   200 |
| Company C | San Francisco | Profit      |   150 |
+-----------+---------------+-------------+-------+

How do I use SQL to transform it to:

+----------+---------------+--------+---------+
|   Name   |   Location    | Sales  | Profit  |
+----------+---------------+--------+---------+
| CompanyA | Houston       |    100 |      50 |
| CompanyB | San Francisco |    500 |     200 |
| CompanyC | New York      |    200 |     150 |
+----------+---------------+--------+---------+

krokodilko · Accepted Answer · 2017-08-13T06:53:29.070

Try either 1 or 2 query: http://sqlfiddle.com/#!6/bb33b/7
The second one gives the exact results shown in your question, but my guess is that rather the first one is what you are looking for.

SELECT Name, Location,
       Sum( Case Description when 'Sales' Then Value else 0 end ) Sales,
       Sum( Case Description when 'Profit' Then Value else 0 end ) Profit
FROM table1
GROUP BY Name, Location
Order by 1,2
;

|      Name |      Location | Sales | Profit |
|-----------|---------------|-------|--------|
| Company A |       Houston |   100 |     50 |
| Company B | San Francisco |   500 |    200 |
| Company C |      New York |   200 |      0 |
| Company C | San Francisco |     0 |    150 |

SELECT Name, Min( Location ) As Location,
       Sum( Case Description when 'Sales' Then Value else 0 end ) Sales,
       Sum( Case Description when 'Profit' Then Value else 0 end ) Profit
FROM table1
GROUP BY Name
Order by 1,2
;
;

|      Name |      Location | Sales | Profit |
|-----------|---------------|-------|--------|
| Company A |       Houston |   100 |     50 |
| Company B | San Francisco |   500 |    200 |
| Company C |      New York |   200 |    150 |

score 0 · Answer 2 · answered Aug 13 '17 at 06:38

Assuming your description values are only ever Sales and Profit, here is a CTE solution. I called the initial table Before.

with cteSales as (select name, value from Before where description = 'Sales')
, cteProfit as (select name, value from Before where description  = 'Profit')
select 
distinct 
    name
    , location
    , (select value from cteSales where name = B.name) as Sales
    , (select value from cteProfit where name = B.name) as Profit
from Before B

score 0 · Answer 3 · answered Aug 13 '17 at 06:52

To transform your data to your desired form, you could do:

 SELECT NAME
          ,LOCATION
          ,SUM (CASE WHEN DESCRIPTION = 'Sales' THEN Values ELSE 0 END) AS SALES
          ,SUM (CASE WHEN DESCRIPTION = 'Profit' THEN Values ELSE 0 END) AS PROFIT
 FROM yourtable
 GROUP BY NAME
               ,LOCATION

score 0 · Answer 4 · answered Aug 13 '17 at 07:08

You can use PIVOT function to transform row values into column however it will provide slightly different but more reasonable output -

SELECT Name, Location, Sales, Profit
FROM
(
    SELECT *
    FROM Table1
) AS SourceTable
PIVOT
(
  MIN(value)
  FOR Description IN (Sales, Profit)
) AS PivotTable
ORDER BY Name, Location;

Will generate the following output -

+-----------+---------------+-------+--------+
| Name      | Location      | Sales | Profit |
+-----------+---------------+-------+--------+
| Company A | Houston       | 100   | 50     |
+-----------+---------------+-------+--------+
| Company B | San Francisco | 500   | 200    |
+-----------+---------------+-------+--------+
| Company C | New York      | 200   | null   |
+-----------+---------------+-------+--------+
| Company C | San Francisco | null  | 150    |
+-----------+---------------+-------+--------+

You can use this as an alternate solution.

SQL Moving a Row to be a column - PIVOT query

4 Answers4