T-SQL : Display logical structured table as flat output

Question

I have a table, T1, with an ID and some other fields.

I have another table, T2, with an ID (which matches the ID in the 1st table), a sort_order and a value. The ID and the order are a composite primary key.

For my output I need to have a single row with ID, then value1, value2, value3 ... value*n* (in practice I'll be stopping at 12)

I can obviously do this by left joining T2 onto T1 multiple times:

SELECT T1.ID, T1.[Other fields], T2_1.Value AS Value1, T2_2.Value AS Value2 ...
FROM T1 
LEFT JOIN T2 AS T2_1 ON T1.ID = T2_1.ID AND 1 = T2_1.Sort_Order
LEFT JOIN T2 AS T2_2 ON T1.ID = T2_2.ID AND 2 = T2_1.Sort_Order
...

But I was wondering if there was a better way? Or at least one that looked neater!

SQL version is 2008.

Thanks.

score 0 · Answer 1 · answered Mar 19 '14 at 11:41

0

My first thought is conditional aggregation:

SELECT T1.ID, T1.[Other fields],
       MAX(CASE WHEN T2.Sort_Order = 1 then T2.Value END) AS Value1,
       MAX(CASE WHEN T2.Sort_Order = 2 then T2.Value END) AS Value2,
       MAX(CASE WHEN T2.Sort_Order = 3 then T2.Value END) AS Value3,
       . . .
FROM T1  LEFT JOIN
     T2 T2
     ON T1.ID = T2_1.ID
GROUP BY t1.ID, T1.[Other fields];

answered Mar 19 '14 at 11:41

Gordon Linoff

1,242,037
58
646
786

That certainly works, as is a lot neater. Why didn't I think of that? I'll see if anyone else has alternative approaches. – the Ben B Mar 19 '14 at 11:47

score 0 · Accepted Answer · edited May 23 '17 at 10:26

On SQL-server, PIVOT is the keyword you're looking for!

Query:

SELECT ID, [Other fields], [1], [2], [3], [4], . . .
FROM (
    SELECT T1.ID, T1.[Other fields], T2.Sort_Order, T2.Value) 
    FROM T1 INNER JOIN T2 ON T1.ID = T2.ID
) AS SourceTable
PIVOT (
    MAX(Value)
    FOR Sort_Order IN ([1], [2], [3], [4], . . .)
) AS PivotTable

Check out BOL for more information: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Using dynamic sql, it is also possible to generate the column list dynamically, so you don't have to limit yourself to a fixed number of values. See for example: T-SQL Pivot? Possibility of creating table columns from row values

T-SQL : Display logical structured table as flat output

2 Answers2