Transposing data

Question

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 |
+---------------+---+---+

Answer 1

In sql-server-2005 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:

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;

Try the SQLFiddle here