Search code examples
sql-serverpivotunpivot

PIVOT table is converting all columns to DATE

Why does my view get created with FIRM_PANEL and FIRM_DATE as DATEs and not an NVARCHAR and a DATE as I expect?

SELECT DISTINCT piv3.CASE_NUMBER, FIRM_PANEL, FIRM_DATE
  FROM
(
SELECT DISTINCT piv2.CASE_NUMBER, piv2.DETAIL_CODE, 
   CASE piv2.DATA_FLAGS 
     WHEN 'TEXT_FLAG'        THEN CONVERT(NVARCHAR, TEXT_VALUE)
     WHEN 'DATE_FLAG'        THEN CONVERT(DATE, DATE_VALUE)    
     END AS 'VALUE'
    FROM
    (
        SELECT DISTINCT CASE_NUMBER, DETAIL_CODE, piv.DATA_FLAGS, TEXT_VALUE, CONVERT(DATE, DATE_VALUE) AS DATE_VALUE
        FROM table
        UNPIVOT
        ( 
          YES_OR_NO
          FOR DATA_FLAGS IN (DATE_FLAG, TEXT_FLAG)
        ) piv 
    ) piv2 

    ) as sourcetable
    PIVOT
    ( 
    MAX(VALUE)
    FOR DETAIL_CODE IN (FIRM_PANEL,
      FIRM_DATE )
    ) AS piv3

enter image description here

Let me explain each nested pivot/select. The first SELECT gives you an idea how the data is store raw. I needed a way to show 1 CASE_NUMBER and DETAIL_CODEs as unique columns.

SELECT DISTINCT CASE_NUMBER, DETAIL_CODE, piv.DATA_FLAGS, TEXT_VALUE, 
CONVERT(DATE, DATE_VALUE) AS DATE_VALUE
    FROM table

enter image description here

I then needed to take DATE_VALUE and TEXT_VALUE and merge into one column called VALUE, hence my UNPIVOT.

SELECT DISTINCT piv2.CASE_NUMBER, piv2.DETAIL_CODE, 
   CASE piv2.DATA_FLAGS 
     WHEN 'TEXT_FLAG'        THEN CONVERT(NVARCHAR, TEXT_VALUE)
     WHEN 'DATE_FLAG'        THEN CONVERT(NVARCHAR, DATE_VALUE)    
     END AS 'VALUE'
    FROM
    (
        SELECT DISTINCT CASE_NUMBER, DETAIL_CODE, piv.DATA_FLAGS, TEXT_VALUE, CONVERT(DATE, DATE_VALUE) AS DATE_VALUE
        FROM table
        UNPIVOT
        ( 
          YES_OR_NO
          FOR DATA_FLAGS IN (DATE_FLAG, TEXT_FLAG)
        ) piv 
    ) piv2

enter image description here

Then my final SQL (top) finally PIVOTs again and removes nulls and interprets if the column was a text field then use NVARCHAR and if date use DATE.

enter image description here

However the only way I can SELECT from the view without errors is making both NVARCHAR or else I'll receive "Conversion failed when converting date and/or time from character string" due to both columns being a DATE field when they shouldn't be. However I do not understand why the view is created with the two DATE columns. My current workaround is setting as string and just doing the conversion when selecting from the view.

Sorry for the wordiness.

Solution

  • I think an-d was on the right path.. You should definitely try case aggregates.

    SELECT  CASE_NUMBER,
        MAX( CASE WHEN DETAIL_CODE = 'FIRM_PANEL' THEN TEXT_VALUE END) AS FIRM_PANEL,
        MAX( CASE WHEN DETAIL_CODE = 'FIRM_DATE' THEN CONVERT(DATE, DATE_VALUE) END) AS FIRM_DATE
FROM    table
GROUP BY CASE_NUMBER

    You can add more where clauses to the case expression if you need to, but the above may work.