我必须从sql Server旋转给定的表,但正常的数据透视表不起作用(据我所知).那么有人知道如何将表格旋转成所需的格式吗？
只是为了使问题更复杂,给定标签的列表可能会有所不同,并且可能会在任何给定时间出现新的标签名称.
鉴于数据
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
想要结果
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
解决方法
这应该把你排除在外.它真的是一个UNPIVOT和一个PIVOT.请注意,您必须符合您的数据,因为UNPIVOT将所有数据放在同一列中.
请注意,我必须在内部动态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) ;
INSERT INTO @src
VALUES (1,'LabelNameThree',21,2.1) ;
INSERT INTO @src
VALUES (1,'LabelNameFour',15,1.5) ;
INSERT INTO @src
VALUES (2,19,1) ;
INSERT INTO @src
VALUES (2,0) ;
INSERT INTO @src
VALUES (2,16,0.9375) ;
INSERT INTO @src
VALUES (2,'LabelNameFive','LabelNameSix',17,1) ;
INSERT INTO @src
VALUES (3,12,0) ;
INSERT INTO @src
VALUES (3,11,0.9167) ;
INSERT INTO @src
VALUES (3,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,4) NOT NULL
) ;
INSERT INTO @src
VALUES (1,''LabelNameOne'',''LabelNameTwo'',''LabelNameThree'',''LabelNameFour'',''LabelNameFive'',''LabelNameSix'',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) ;
