在多个列上进行动态枢纽的挣扎中，其中一列被串联 [英] Struggling with a dynamic pivot on multiple columns with one being concatenated
问题描述
我有一个数据表，如下所示:
I have a table with data like so:
Employee PRDate Type Code Amount Subject Eligible
1234 1/1/2015 D 1 100.00 100.00 0.00
1234 1/1/2015 D 2 200.00 0.00 0.00
5678 1/1/2015 D 1 500.00 40.00 500.00
1234 1/1/2015 E 1 300.00 30.00 300.00
5678 1/1/2015 E 1 700.00 700.00 500.00
1234 1/1/2015 E 2 400.00 200.00 0.00
1234 1/8/2015 L 55 40.00 40.00 40.00
我需要这样显示数据:
Employee PRDate D1Amt D1Subj D1Elig D2Amt D2Subj D2Elig E1Amt E1Subj E1Elig E2Amt E2Subj E2Elig L55Amt L55Subj L55Elig
1234 1/1/2015 100.00 100.00 0.00 200.00 0.00 0.00 300.00 30.00 300.00 400.00 200.00 0.00 40.00 40.00 40.00
4678 1/1/2015 500.00 40.00 500.00 700.00 700.00 500.00
我可以绕一列，但是当我尝试将Type列和Code列组合在一起时，却遇到了转换错误(Type是varchar，而code是tinyint).除了动态数据透视外，我不确定如何获得所需的结果.能否达到预期的结果?
I can pivot on one column but when I try combining the Type and Code columns to get the one I get conversion errors (Type is a varchar and code is a tinyint). I'm not sure how to get to the desired results other than dynamic pivot. Can the desired results be achieved?
到目前为止，我还没弄清楚如何组合类型，代码和每个money列(金额，主题和合格)，以在正确的列下获取数据.
I've gotten this far but I can't figure out how to combine the type, code and each money columns (amount, subject and eligible) to get the data under the correct columns.
IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp285865%')
DROP TABLE #temp285865;
Create table dbo.#temp285865
(
EDLCodetemp varchar(10)
);
INSERT INTO #temp285865
(
[EDLCodetemp]
)
SELECT DISTINCT EDLCode
FROM #results
ORDER BY EDLCode;
-- Building a comma separated list of EDLCodes in #edltemp
DECLARE @cols varchar(1000);
SELECT @cols = COALESCE(@cols + ',[' + [EDLCodetemp] + ']', '[' + [EDLCodetemp] + ']')
FROM #temp285865;
-- Building the query appending columns
DECLARE @query varchar(4000);
SET @query =
'SELECT [CoName],
[PRCo],
[PRGroup],
[PREndDate],
[PaySeq],
[EDLType],
[Hours],
[SubjectAmt],
[EligibleAmt],
[PaidMth],
[LastName],
[FirstName],
[UseOver],
[OverAmt],
[Amount],
[PRGRDescrip],
[LimitPeriod],
[LimitMth],
[PREHEmployee],
[SortName],
[PaybackAmt],
[PaybackOverAmt],
[PaybackOverYN],
[PRDTEmployee],
[TrueEarns], '
+ @cols + ' FROM
(
SELECT [CoName],
[PRCo],
[PRGroup],
[PREndDate],
[PaySeq],
[EDLType],
[Hours],
[SubjectAmt],
[EligibleAmt],
[PaidMth],
[LastName],
[FirstName],
[PRDLDescrip],
[PRECDescrip],
[UseOver],
[OverAmt],
[Amount],
[PRGRDescrip],
[LimitPeriod],
[LimitMth],
[PREHEmployee],
[SortName],
[PaybackAmt],
[PaybackOverAmt],
[PaybackOverYN],
[PRDTEmployee],
[TrueEarns],
[EDLCode]
FROM #results
) p
PIVOT (
MAX(EDLCode)
FOR [EDLCode] IN (' + @cols + ')
)
as pvt';
EXEC(@query);
DROP TABLE #temp285865;
推荐答案
下面的带有动态SQL的PIVOT将根据您提供的输入数据为您提供所需的结果(不过我在最后一行更改了PRDate).
The following PIVOT with dynamic SQL would give you the result you want, based on the input data you provided (I changed the PRDate in the last row though).
第一条语句使用所需的列名和关联的值构建中间表#bt.然后在@cols中为动态SQL语句构建列名.最后，中间表#bt通过动态SQL语句使用@cols进行透视.
The first statement builds an intermediate table #bt with the column names you want and the associated value. Then the column names are built in @cols for the dynamic SQL statement. Finally the intermediate table #bt is pivoted with a dynamic SQL statement using the @cols to pivot.
SET NOCOUNT ON;
CREATE TABLE #t(
Employee INT,
PRDate DATETIME,
Type CHAR(1),
Code TINYINT,
Amount DECIMAL(28,2),
Subject DECIMAL(28,2),
Eligible DECIMAL(28,2)
);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(1234,'2015-01-01','D',1,100.00,100.00,0.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(1234,'2015-01-01','D',2,200.00,0.00,0.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(5678,'2015-01-01','D',1,500.00,40.00,500.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(1234,'2015-01-01','E',1,300.00,30.00,300.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(5678,'2015-01-01','E',1,700.00,700.00,500.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(1234,'2015-01-01','E',2,400.00,200.00,0.00);
INSERT INTO #t(Employee,PRDate,Type,Code,Amount,Subject,Eligible)VALUES(1234,'2015-01-01','L',55,40.00,40.00,40.00);
SELECT
Employee,
PRDate,
Type+CAST(Code AS VARCHAR(3))+ca.name AS colname,
ca.val
INTO
#bt
FROM
#t
CROSS APPLY(
SELECT Amount AS val,'Amt' AS name
UNION ALL
SELECT Subject AS val,'Subj' AS name
UNION ALL
SELECT Eligible AS val,'Elig' AS name
) AS ca;
/* If you need to SUM for all dates, instead use this statement to create #bt
SELECT
Employee,
Type+CAST(Code AS VARCHAR(3))+ca.name AS colname,
ca.val
INTO
#bt
FROM
(
SELECT
Employee,
Type,
Code,
SUM(Amount) AS Amount,
SUM(Subject) AS Subject,
SUM(Eligible) AS Eligible
FROM
#t
GROUP BY
Employee,
Type,
Code
) AS t
CROSS APPLY(
SELECT Amount AS val,'Amt' AS name
UNION ALL
SELECT Subject AS val,'Subj' AS name
UNION ALL
SELECT Eligible AS val,'Elig' AS name
) AS ca;
*/
DECLARE @cols VARCHAR(8000);
SET @cols=STUFF(
(SELECT DISTINCT
',['+colname+']'
FROM
#bt
FOR XML PATH('')),
1,
1,
''
);
DECLARE @sql VARCHAR(MAX);
SET @sql='
SELECT
*
FROM
#bt
PIVOT(
MAX(val)
FOR colname IN ('+@cols+')
) AS piv
';
EXEC (@sql);
DROP TABLE #bt;
DROP TABLE #t;
结果如下:
Employee PRDate D1Amt D1Elig D1Subj D2Amt D2Elig D2Subj E1Amt E1Elig E1Subj E2Amt E2Elig E2Subj L55Amt L55Elig L55Subj
1234 2015-01-01 100.00 0.00 100.00 200.00 0.00 0.00 300.00 300.00 30.00 400.00 0.00 200.00 40.00 40.00 40.00
5678 2015-01-01 500.00 500.00 40.00 NULL NULL NULL 700.00 500.00 700.00 NULL NULL NULL NULL NULL NULL
这篇关于在多个列上进行动态枢纽的挣扎中，其中一列被串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
