How to convert flattened SQL Table to Rows (approximately UNPIVOTting)

Question

Imagine the source table schema:

Table1: ID*, ValueA, ValueB, ValueX1, ValueX2, ValueX3, ValueY1, ValueY2, ValueY3

And the destination table Schema:

TableA: ID*, ValueA, ValueB
TableB: ID*, Type*, Value1, Value2, Value3

Where a single row in Table1 is represented by 1 row in TableA and 2 rows in TableB (One with Type X, the other with Type Y)

---

Yes, the existence of the former schema makes me sad. No, I didn't write it. No, I can't directly change it. :(

---

I want to right a query to convert Table1 into TableB (obviously, getting TableA is easy.)

I know how to do this with a UNION ALL, but that's going to be ugly. (Not least because (X,Y) is actually (P,Q,R,S,T,U,V,W,X,Y,Z), and 1 ... 3 is actually 1 ... 15. Sigh)

I feel like PIVOT/UNPIVOT are sort of vaguely close, but I can't see anything that specifically deals with this - I always see something that's only moving single columns, or sets of single columns.

Can this be done elegantly, in T-SQL, in any sense?

Answer 1

You are looking for this

Cross Apply method:(preferred)

SELECT ID, tc.Type, tc.Value1, tc.Value2, tc.Value3
FROM   yourtable
        CROSS apply (VALUES (ValueX1,ValueX2,ValueX3,'X'),
                            (ValueY1,ValueY2,ValueY3,'Y') )tc (Value1, Value2, Value3, Type) 

UNION ALL method

SELECT ID,type = 'X',ValueX1,ValueX2,ValueX3
FROM   yourtable
UNION ALL
SELECT ID,'Y', ValueY1,ValueY2,ValueY3
FROM   yourtable 

Note both the approaches consider that, the datatype of ValueX1 and ValueY1 is implicitly convertible or same. Same goes to remaining pairs as well