Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to use Excel to solve this problem. Can this be done by using SQL or is there any tool that can do this?

Original table

+----+--------------+--------------+-----+---------------+---------------+
| ID | price_date_1 | price_date_2 | ... | price_date_30 | price_date_31 |
+----+--------------+--------------+-----+---------------+---------------+
| A  |            1 |            2 | ... |             0 |             1 |
| B  |            2 |            3 | ... |             1 |             2 |
+----+--------------+--------------+-----+---------------+---------------+

New table

+---------------+---+---+
|     Price     | A | B |
+---------------+---+---+
| price_date_1  | 1 | 2 |
| price_date_2  | 2 | 3 |
| ...           |   |   |
| price_date_30 | 0 | 1 |
| price_date_31 | 1 | 2 |
+---------------+---+---+
share|improve this question
up vote 6 down vote accepted

In or later, the the UNPIVOT and PIVOT operators can be used:

Sample data:

DECLARE @Source AS TABLE
(
    ID      char(1) PRIMARY KEY,
    PD1     integer NOT NULL,
    PD2     integer NOT NULL,
    PD3     integer NOT NULL,
    PD4     integer NOT NULL,
    PD5     integer NOT NULL
);

INSERT @Source
    (ID, PD1, PD2, PD3, PD4, PD5)
VALUES
    ('A', 1, 2, 3, 4, 5),
    ('B', 6, 7, 8, 9, 0);

Query:

SELECT 
    Pvt.Price, 
    Pvt.A, 
    Pvt.B
FROM @Source AS s
UNPIVOT
(
    Val 
    FOR Price IN (PD1, PD2, PD3, PD4, PD5)
) AS Unpvt
PIVOT 
(
    MAX(Val) 
    FOR ID IN (A, B)
) AS Pvt;

Output:

╔═══════╦═══╦═══╗
║ Price ║ A ║ B ║
╠═══════╬═══╬═══╣
║ PD1   ║ 1 ║ 6 ║
║ PD2   ║ 2 ║ 7 ║
║ PD3   ║ 3 ║ 8 ║
║ PD4   ║ 4 ║ 9 ║
║ PD5   ║ 5 ║ 0 ║
╚═══════╩═══╩═══╝

Execution plan: Unpivot/pivot plan

The query can also be written without PIVOT and UNPIVOT:

SELECT
    f1.Price,
    A = MAX(CASE WHEN s.ID = 'A' THEN f1.Val END),
    B = MAX(CASE WHEN s.ID = 'B' THEN f1.Val END)
FROM @Source AS s
CROSS APPLY
(
    SELECT 'PD1', s.PD1 UNION ALL
    SELECT 'PD2', s.PD2 UNION ALL
    SELECT 'PD3', s.PD3 UNION ALL
    SELECT 'PD4', s.PD4 UNION ALL
    SELECT 'PD5', s.PD5
) AS f1 (Price, Val)
GROUP BY
    f1.Price;

Without PIVOT/UNPIVOT

Try the SQLFiddle here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.