2005 SSRS/SQL Server PIVOT results need reversing

Question

Preamble: I've read through the three questions/answers here,here, and here, with big ups to @cade-roux. This all stemmed from trying to use the following data in a 2005 SSRS matrix that, I believe, doesn't work because I want to show a member having to take a test multiple times, and SSRS seems to require the aggregate where I want to show all dates.

I get the following results in my table, which seems to be showing all the data correctly: Table

How do I change the code below to show a) the "tests" at the top of each column with b) if it's called for, the multiple dates that test was taken?

Here's the code I have to produce the table, above. Much of it is commented out as I was just trying to get the pivot to work, but you may notice I am also trying to specify which test column comes first.

    CREATE TABLE #tmp (     ---THIS WORKS BUT TESTS ARE VERTICAL
    [TEST] [varchar](30) NOT NULL,
    [ED] [datetime] NOT NULL
)

--WHERE THE TEST AND ED COME FROM
INSERT #TMP
SELECT DISTINCT
--      N.FULL_NAME
--      , CONVERT(VARCHAR(30), AM.CREATEDATE, 101) AS ACCOUNT_CLAIMED
--      , N.EMAIL
--      , NULL AS 'BAD EMAIL'
--      , CONVERT(VARCHAR(30), AC.EFFECTIVE_DATE, 101) AS EFFECTIVE_DATE
         AC.PRODUCT_CODE AS TEST
        , CONVERT(VARCHAR(30), AC.EFFECTIVE_DATE, 101) AS ED
--      , CASE
--          WHEN AC.PRODUCT_CODE = 'NewMem_Test' THEN '9'
--          WHEN AC.PRODUCT_CODE = 'NM_Course1' THEN '1'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course1' THEN '2'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course2' THEN '3'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course3' THEN '4'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course4' THEN '5'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course5' THEN '6'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course6' THEN '7'
--          WHEN AC.PRODUCT_CODE = 'NMEP_Course7' THEN '8'
--      END AS 'COLUMN_ORDER'
    FROM NAME N
        JOIN USERMAIN UM
        ON N.ID = UM.CONTACTMASTER
        JOIN formTransLog TL
        ON UM.USERID = TL.USERNAME
        JOIN anet_Users AU
        ON UM.USERID = AU.USERNAME
        JOIN anet_Membership AM
        ON AU.USERID = AM.USERID
        JOIN ACTIVITY AC
        ON N.ID = AC.ID
        AND AC.ACTIVITY_TYPE = 'COURSE'
        AND AC.PRODUCT_CODE LIKE 'N%'
--ORDER BY 1, 7

DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
        ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
    SELECT DISTINCT PIVOT_CODE
    FROM (
        SELECT TEST, ED, ROW_NUMBER() OVER (PARTITION BY TEST ORDER BY ED) AS PIVOT_CODE
        FROM #tmp
    ) AS rows
) AS PIVOT_CODES

SET @sql = '
;WITH p AS (
    SELECT TEST, ED, ROW_NUMBER() OVER (PARTITION BY TEST ORDER BY ED) AS PIVOT_CODE
    FROM #tmp
)
SELECT TEST, ' + @select_list + '
FROM p
PIVOT (
    MIN(ED)
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
'

PRINT @sql

EXEC (@sql)

EDIT: The goal is to have the report in SSRS look like this: Goal

Can you show the sample layout you are looking for? – Anup Agrawal May 01 '14 at 15:18 — Anup Agrawal, May 01 '14 at 15:18

score 1 · Accepted Answer · answered Jun 25 '14 at 14:43

I was able to produce the results you were looking for by adding in a number (RowNum) to the query underneath the PIVOT operator. It doesn't have to be in the final query (though you might want it for client-side sorting), but by having it in the underlying layer the PIVOT operation treats that number like a member of a GROUP BY clause.

Please look through my sample SQL below and let me know if this matches your criteria.

CREATE TABLE #TMP
  (
    Name VARCHAR(10),
    Test VARCHAR(20),
    EffectiveDate DATETIME
  )
INSERT INTO #TMP (Name, Test, EffectiveDate)
SELECT 'Jane', 'NM_Course1', '01/17/2014' UNION
SELECT 'Jane', 'NMEP_Course1', '12/19/2013' UNION
SELECT 'Jane', 'NMEP_Course1', '12/20/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '12/19/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '12/22/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '01/05/2014' UNION
SELECT 'John', 'NM_Course1', '01/17/2014' UNION
SELECT 'John', 'NMEP_Course1', '01/11/2014' 



DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
        ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
    SELECT DISTINCT PIVOT_CODE
    FROM (
        SELECT TEST AS PIVOT_CODE
        FROM #tmp
    ) AS rows
) AS PIVOT_CODES

SET @sql = '
SELECT Name, ' + @select_list + '
FROM 
  (
    SELECT b.Name, RowNum, b.EffectiveDate, b.TEST AS PIVOT_CODE
    FROM 
          (
            SELECT Name, Test, EffectiveDate, ROW_NUMBER() OVER (PARTITION BY NAME, TEST ORDER BY EffectiveDate) RowNum 
            FROM #Tmp
          ) b
  ) p
PIVOT (
    MIN(EffectiveDate)
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
ORDER BY Name, RowNum
'


PRINT @sql

EXEC (@sql)


DROP TABLE #TMP

2005 SSRS/SQL Server PIVOT results need reversing

1 Answers1