SQL use rows as columns

Question

How can I convert the following:

+------+---------+------+
| Date | Product | Sold |
+------+---------+------+
| 1/1  | P1      |  100 |
| 1/1  | P2      |  250 |
| 1/1  | P3      |   50 |
| 2/1  | P1      |  170 |
| 2/1  | P2      |    0 |
| 2/1  | P3      |   70 |
+------+---------+------+

Into a table like this:

+------+-----+-----+----+
| Date | P1  | P2  | P3 |
+------+-----+-----+----+
| 1/1  | 100 | 250 | 50 |
| 2/1  | 170 |   0 | 70 |
+------+-----+-----+----+

Using SQL?
I know the number of products (exactly 3).

Please look up pivots. Which database are you using?

Chetter Hummin
– Chetter Hummin

2012-03-19 14:10:36 +00:00
Commented Mar 19, 2012 at 14:10 — Chetter Hummin
– Chetter Hummin, Commented Mar 19, 2012 at 14:10
PIVOT. which database are you using?

Randy
– Randy

2012-03-19 14:10:57 +00:00
Commented Mar 19, 2012 at 14:10 — Randy
– Randy, Commented Mar 19, 2012 at 14:10
@AmitBhargava I am using MySQL

Baruch
– Baruch

2012-03-19 14:14:20 +00:00
Commented Mar 19, 2012 at 14:14 — Baruch
– Baruch, Commented Mar 19, 2012 at 14:14

ruakh · Accepted Answer · 2012-03-19 14:11:46Z

5

Many DBMSes offer specific support for "pivots" or "crosstab reports", but if your DBMS does not, or if you prefer a solution that will work on a variety of DBMSes, you can write:

SELECT Date,
       MIN(CASE WHEN Product = 'P1' THEN Sold END) AS "P1",
       MIN(CASE WHEN Product = 'P2' THEN Sold END) AS "P2",
       MIN(CASE WHEN Product = 'P3' THEN Sold END) AS "P3"
  FROM ___insert_table_name_here___
 GROUP
    BY Date
;

Edited to add: You've now mentioned that you're using MySQL, which does not have special pivot or crosstab support, so the above the query is the way to go.

answered Mar 19, 2012 at 14:11

ruakh

185k29 gold badges292 silver badges324 bronze badges

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

Comments

Michael Rice · Accepted Answer · 2012-03-19 14:20:31Z

0

If it is SQL Server 2005+:

SELECT
    Date 
    , [P1]
    , [P2]
    , [P3]
FROM
(
    SELECT
        Date
        , Product
        , Sold
    FROM
        ProductSold
) AS t1
PIVOT
(
    MIN(Sold)
    FOR Product IN ([P1], [P2], [P3])
) AS pvt
ORDER BY
    Date ASC;

answered Mar 19, 2012 at 14:20

Michael Rice

1,1835 silver badges13 bronze badges

Comments

Taryn · Accepted Answer · 2012-10-01 21:06:14Z

In MySQL if you have an unknown number of columns to rotate, then you can use prepared statements and your code will look like this (See SQL Fiddle with Demo):

CREATE TABLE Table1
    (`Date` datetime, `Product` varchar(7), `Sold` int)
;

INSERT INTO Table1
    (`Date`, `Product`, `Sold`)
VALUES
    ('2001-01-01 00:00:00', 'P1', 100),
    ('2001-01-01 00:00:00', 'P2', 250),
    ('2001-01-01 00:00:00', 'P3', 50),
    ('2001-02-01 00:00:00', 'P1', 170),
    ('2001-02-01 00:00:00', 'P2', 0),
    ('2001-02-01 00:00:00', 'P3', 70)
;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Product = ''',
      Product,
      '''  then sold else 0 end) AS ',
      Product
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT Date, ', @sql, ' 
                  FROM table1 
                  GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Collectives™ on Stack Overflow

SQL use rows as columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related