Transforming an SQL table using Unpivot

Question

If I have a table with the following structure

ID NAME A1 A2 A3 B1 B2 B3

X----Y------0---1---2---3---4---5 (dashes are just to push values under headers)

How do I transform it to be the following:

ID NAME LETTER 1 2 3

X----Y----------A------0-1-2

X---Y------------B-----3-4-5

Answer 1

SELECT id, name, 'A' letter, a1 1, a2 2, a3 3 from example_table
UNION
SELECT id, name, 'B' letter, b1 1, b2 2, b3 3 from example_table

I'm not sure that 1,2,3 will work as column names; that may be RDBMS-dependant.

Answer 2

While I don't consider this a feasible answer, it may give you something to think about as it produces the end result in sql server.

SELECT 
    upvt.id
    , upvt.name
    , left(label,1) AS letter
    , CASE
        WHEN left(label,1) = 'A' THEN MIN(value)
        WHEN left(label,1) = 'B' THEN MAX(value)
        END AS [1]
    , CASE
        WHEN left(label,1) = 'A' THEN MIN(value1)
        WHEN left(label,1) = 'B' THEN MAX(value1)
        END AS [2]
    , CASE
        WHEN left(label,1) = 'A' THEN MIN(value2)
        WHEN left(label,1) = 'B' THEN MAX(value2)
        END AS [3]
FROM example_table
UNPIVOT (
    value
    FOR label IN (a1,b1)
    ) upvt
INNER JOIN (
            SELECT 
                id
                , name
                , left(label1,1) AS letter
                , value1
            FROM example_table
            UNPIVOT (
                value1
                FOR label1 IN (a2,b2)
                ) upvt1 ) as b
    ON upvt.id = b.id
    AND upvt.name = b.name
INNER JOIN (
            SELECT 
                id
                , name
                , left(label2,1) AS letter
                , value2
            FROM example_table
            UNPIVOT (
                value2
                FOR label2 IN (a3,b3)
                ) upvt2 ) c
    ON upvt.id = c.id
    AND upvt.name = c.name
group by upvt.id, upvt.name, upvt.label

Not exactly what I would call "production ready".