与多个列动态枢轴与一个挣扎被级联(Struggling with a dynamic pivot o

我有一个像这样的数据表：

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

我可以转动的一列，但是当我尝试结合的类型和代码列，以获得一个我得到的转换错误（类型是一个varchar和代码是TINYINT）。我不知道怎么去比动态旋转等所期望的结果。可以预期的结果可以实现吗？

到目前为止，我已经得到了这一点，但我无法弄清楚如何将类型，代码和每个货币列（金额，主题和资格）结合起来，以获得正确的列下的数据。

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虽然）。

第一条语句建立一个中间表#bt你想要的列名和相关的值。然后，列名都建在@cols的动态SQL语句。最后，中间表#bt转动与使用动态SQL语句@cols转动。

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