Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
What's your table in? Is it in a database? If so, what RDBMS are you using? Is this just in Excel? –  billinkc Dec 20 '12 at 3:58
    
@SQL Kiwi: Thank you for editing the example, it looks much more better :) –  user16388 Dec 26 '12 at 6:04
    
@bilinkc: My table in stored in a database and I'm using Microsoft SQL Server. –  user16388 Dec 26 '12 at 6:04
add comment

1 Answer

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
    
Thank you so much for the answer, this really helps me! –  user16388 Dec 21 '12 at 3:32
    
oh I'm sorry, I'm new to this site so I'm still not get used to the regulations. I've accepted the answer, thanks! –  user16388 Dec 26 '12 at 6:07
add comment

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.