CODEWITHSUNDEEP
mysqlsqlpivot
Baruch
Baruch

Reputation: 21508

SQL use rows as columns

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).

Upvotes: 0

Views: 241

Answers (3)

Taryn
Taryn

Reputation: 247650

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;

Upvotes: 0

Michael Rice
Michael Rice

Reputation: 1183

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;

Upvotes: 0

ruakh
ruakh

Reputation: 183241

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.

Upvotes: 5

Related Questions