CODEWITHSUNDEEP
sql-serverpivotunpivot
xhermit
xhermit

Reputation: 501

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.

Upvotes: 0

Views: 103

Answers (2)

JamieD77
JamieD77

Reputation: 13949

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.

Upvotes: 1

an-d
an-d

Reputation: 11

I don't quite understand the reasons for your use of pivots, but the problem seems to be that you're trying to cast the same column VALUE as both DATE and NVARCHAR here -

...
CASE piv2.DATA_FLAGS 
    WHEN 'TEXT_FLAG'        THEN CONVERT(NVARCHAR, TEXT_VALUE)
    WHEN 'DATE_FLAG'        THEN CONVERT(DATE, DATE_VALUE)    
    END AS 'VALUE'
...

You can't control when SQL Server performs the CONVERT operation and it might do it before checking the DATA_FLAGS. Your query should work if you try to move the CONVERT outside to the first select -

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

If you're open to not using pivots, here's a simple alternative to get the same result -

SELECT CASE_NUMBER
    , MAX(CASE WHEN DATA_FLAGS = 'TEXT_FLAG' THEN TEXT_VALUE END) FIRM_DATE
    , MAX(CASE WHEN DATA_FLAGS = 'DATE_FLAG' THEN CONVERT(DATE, DATE_VALUE) END) FIRM_PANEL
FROM #in
GROUP BY CASE_NUMBER

This gives the same result as you're looking for with the columns in their expected data types. Note that it assumes you will have only one record with TEXT_FLAG and DATE_FLAG each for every CASE_NUMBER.

Upvotes: 0

Related Questions