I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format?
我必须从SQL Server旋转给定的表,但正常的数据透视表不起作用(据我所知)。那么有人知道如何将表格旋转成所需的格式吗?
Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time.
只是为了使问题更复杂,给定标签的列表可能会有所不同,并且可能会在任何给定时间出现新的标签名称。
Given 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
Wanted 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
3 个解决方案
#1
5
This should sort you out. It's really an UNPIVOT and a PIVOT. Note that you have to conform your data because the UNPIVOT puts all the data in the same column.
这应该把你排除在外。它真的是一个UNPIVOT和一个PIVOT。请注意,您必须符合您的数据,因为UNPIVOT将所有数据放在同一列中。
Note that I had to recreate/repopulate the table variable in the inner dynamic SQL - typically this is not necessary when dealing with a permanent table.
请注意,我必须在内部动态SQL中重新创建/重新填充表变量 - 通常在处理永久表时不需要这样做。
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) ;
#2
1
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
... similar query with Denominator ...
...与Denominator的类似查询...
union all
... similar query with Ratio...
...与Ratio的类似查询...
#3
1
What you seek is dynamic cross tab. The short answer is that it cannot be done in T-SQL without some fugly dynamic SQL. The Hoyle answer is that you should pivot the data in a reporting tool or in the middle tier.
您寻求的是动态交叉表。简短的回答是,如果没有一些简单的动态SQL,它就无法在T-SQL中完成。霍伊尔的答案是你应该在报告工具或中间层中转动数据。
#1
5
This should sort you out. It's really an UNPIVOT and a PIVOT. Note that you have to conform your data because the UNPIVOT puts all the data in the same column.
这应该把你排除在外。它真的是一个UNPIVOT和一个PIVOT。请注意,您必须符合您的数据,因为UNPIVOT将所有数据放在同一列中。
Note that I had to recreate/repopulate the table variable in the inner dynamic SQL - typically this is not necessary when dealing with a permanent table.
请注意,我必须在内部动态SQL中重新创建/重新填充表变量 - 通常在处理永久表时不需要这样做。
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) ;
#2
1
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
... similar query with Denominator ...
...与Denominator的类似查询...
union all
... similar query with Ratio...
...与Ratio的类似查询...
#3
1
What you seek is dynamic cross tab. The short answer is that it cannot be done in T-SQL without some fugly dynamic SQL. The Hoyle answer is that you should pivot the data in a reporting tool or in the middle tier.
您寻求的是动态交叉表。简短的回答是,如果没有一些简单的动态SQL,它就无法在T-SQL中完成。霍伊尔的答案是你应该在报告工具或中间层中转动数据。