How to make an uncomfortable sql table core in SQL Server 2005?

Question

I need to rotate a given table with SQL Server, but a regular core just doesn't work (as far as I tried). So does anyone know how to rotate a table in the desired format?

To make the problem more complicated, the list of predefined labels may change, and it is possible that a new label name may appear at any time.

Data

ID | Label | Numerator | Denominator | Ratio ---+-----------------+-------------+---------------+-------- 1 | LabelNameOne | 41 | 10 | 4,1 1 | LabelNameTwo | 0 | 0 | 0 1 | LabelNameThree | 21 | 10 | 2,1 1 | LabelNameFour | 15 | 10 | 1,5 2 | LabelNameOne | 19 | 19 | 1 2 | LabelNameTwo | 0 | 0 | 0 2 | LabelNameThree | 15 | 16 | 0,9375 2 | LabelNameFive | 19 | 19 | 1 2 | LabelNameSix | 17 | 17 | 1 3 | LabelNameOne | 12 | 12 | 1 3 | LabelNameTwo | 0 | 0 | 0 3 | LabelNameThree | 11 | 12 | 0,9167 3 | LabelNameFour | 12 | 12 | 1 3 | LabelNameSix | 0 | 1 | 0 

Desired Result

 ID | ValueType | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix ---+-------------+--------------+--------------+----------------+---------------+---------------+-------------- 1 | Numerator | 41 | 0 | 21 | 15 | | 1 | Denominator | 10 | 0 | 10 | 10 | | 1 | Ratio | 4,1 | 0 | 2,1 | 1,5 | | 2 | Numerator | 19 | 0 | 15 | | 19 | 17 2 | Denominator | 19 | 0 | 16 | | 19 | 17 2 | Ratio | 1 | 0 | 0,9375 | | 1 | 1 3 | Numerator | 12 | 0 | 11 | 12 | | 0 3 | Denominator | 12 | 0 | 12 | 12 | | 1 3 | Ratio | 1 | 0 | 0,9167 | 1 | | 0 

Answer 1

This should help you figure it out. This is truly UNPIVOT and PIVOT. Note that you must match your data, as UNPIVOT puts all the data in the same column.

Please note that I had to recreate / re-populate the table variable in internal dynamic SQL - this is usually not necessary when working with a permanent table.

 SET NOCOUNT ON ; DECLARE @pivot_cols AS varchar(max) ; DECLARE @src AS TABLE ( ID int NOT NULL ,Label varchar(14) NOT NULL ,Numerator int NOT NULL ,Denominator int NOT NULL ,Ratio decimal(5, 4) NOT NULL ) ; DECLARE @label_order AS TABLE ( Label varchar(14) NOT NULL ,Sort int NOT NULL ) INSERT INTO @src VALUES (1, 'LabelNameOne', 41, 10, 4.1) ; INSERT INTO @src VALUES (1, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (1, 'LabelNameThree', 21, 10, 2.1) ; INSERT INTO @src VALUES (1, 'LabelNameFour', 15, 10, 1.5) ; INSERT INTO @src VALUES (2, 'LabelNameOne', 19, 19, 1) ; INSERT INTO @src VALUES (2, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (2, 'LabelNameThree', 15, 16, 0.9375) ; INSERT INTO @src VALUES (2, 'LabelNameFive', 19, 19, 1) ; INSERT INTO @src VALUES (2, 'LabelNameSix', 17, 17, 1) ; INSERT INTO @src VALUES (3, 'LabelNameOne', 12, 12, 1) ; INSERT INTO @src VALUES (3, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (3, 'LabelNameThree', 11, 12, 0.9167) ; INSERT INTO @src VALUES (3, 'LabelNameFour', 12, 12, 1) ; INSERT INTO @src VALUES (3, 'LabelNameSix', 0, 1, 0) ; INSERT INTO @label_order VALUES ('LabelNameOne', 1) ; INSERT INTO @label_order VALUES ('LabelNameTwo', 2) ; INSERT INTO @label_order VALUES ('LabelNameThree', 3) ; INSERT INTO @label_order VALUES ('LabelNameFour', 4) ; INSERT INTO @label_order VALUES ('LabelNameFive', 5) ; INSERT INTO @label_order VALUES ('LabelNameSix', 6) ; WITH Labels AS ( SELECT DISTINCT src.Label ,ISNULL(label_order.Sort, 0) AS Sort FROM @src AS src LEFT JOIN @label_order AS label_order ON src.label = label_order.label ) SELECT @pivot_cols = COALESCE(@pivot_cols + ',', '') + QUOTENAME(Label, '[') FROM Labels ORDER BY Sort ,Label ; DECLARE @template AS varchar(max) ; SET @template = ' DECLARE @src AS TABLE ( ID int NOT NULL ,Label varchar(14) NOT NULL ,Numerator int NOT NULL ,Denominator int NOT NULL ,Ratio decimal(5, 4) NOT NULL ) ; INSERT INTO @src VALUES (1, ''LabelNameOne'', 41, 10, 4.1) ; INSERT INTO @src VALUES (1, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (1, ''LabelNameThree'', 21, 10, 2.1) ; INSERT INTO @src VALUES (1, ''LabelNameFour'', 15, 10, 1.5) ; INSERT INTO @src VALUES (2, ''LabelNameOne'', 19, 19, 1) ; INSERT INTO @src VALUES (2, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (2, ''LabelNameThree'', 15, 16, 0.9375) ; INSERT INTO @src VALUES (2, ''LabelNameFive'', 19, 19, 1) ; INSERT INTO @src VALUES (2, ''LabelNameSix'', 17, 17, 1) ; INSERT INTO @src VALUES (3, ''LabelNameOne'', 12, 12, 1) ; INSERT INTO @src VALUES (3, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (3, ''LabelNameThree'', 11, 12, 0.9167) ; INSERT INTO @src VALUES (3, ''LabelNameFour'', 12, 12, 1) ; INSERT INTO @src VALUES (3, ''LabelNameSix'', 0, 1, 0) ; WITH src_conformed AS ( SELECT ID ,Label ,CAST (Numerator AS decimal(10, 4)) AS Numerator ,CAST (Denominator AS decimal(10, 4)) AS Denominator ,CAST (Ratio AS decimal(10, 4)) AS Ratio FROM @src ), UNPIVOTED AS ( SELECT * FROM src_conformed UNPIVOT ( Val FOR Col IN (Numerator, Denominator, Ratio) ) AS unpvt ) SELECT * FROM UNPIVOTED PIVOT ( SUM(Val) FOR Label IN ({@pivot_cols}) ) AS pvt ORDER BY ID ,Col ;' ; SET @template = REPLACE(@template, '{@pivot_cols}', @pivot_cols) ; EXEC (@template) ; 

Answer 2

 select id, 'Numerator' as ValueType, case when label = labelNameOne then Numerator else 0 end as LabelNameOne, case when label = labelNameTwo then Numerator else 0 end as LabelNameTwo, case when label = labelNameTree then Numerator else 0 end as LabelNameTree, case when label = labelNameFour then Numerator else 0 end as LabelNameFour, case when label = labelNameFive then Numerator else 0 end as LabelNameFive, case when label = labelNameSix then Numerator else 0 end as LabelNameSix union All 

... a similar query with the denominator ...

 union all 

... a similar query with a coefficient ...

Answer 3

What you are looking for is a dynamic cross tab. The short answer is that it cannot be executed in T-SQL without some dynamic dynamic SQL. Hoyle's answer is that you have to collapse data in a reporting tool or at an average level.