CODEWITHSUNDEEP
t-sqlsql-server-2008-r2pivot
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

NULL values returned by pivot

I have a dataset like the one below (first column created using ROW_NUMNBER OVER PARTITION):

RNEEAN  NEEAN    GFCUS  SCSAC
NEAN1   9432806  94328  GE
NEAN2   9432807  94328  GE
NEAN3   94328472 94328  ES
NEAN4   9432848  94328  ES
NEAN5   9432875  94328  HK
NEAN6   9432876  94328  HK
NEAN7   9432877  94328  HK
NEAN8   9432878  94328  HK
NEAN9   9432879  94328  HK

I can get this dataset with the below query:

select RNEEAN, NEEAN, GFCUS, SCSAC from #tmp where GFCUS=094328

Now I want to pivot the column NEEAN by RNEEAN so I tried the below:

SELECT MAX([NEEAN1]) [NEEAN1],MAX([NEEAN2]) [NEEAN2],MAX([NEEAN3]) [NEEAN3],
MAX([NEEAN4]) [NEEAN4], MAX([NEEAN5]) [NEEAN5],MAX([NEEAN6]) [NEEAN6],MAX([NEEAN7]    [NEEAN7], MAX([NEEAN8])  [NEEAN8], MAX([NEEAN9]) [NEEAN9], GFCUS
from #tmp 
PIVOT(
    min(NEEAN) for RNEEAN in ([NEEAN1],[NEEAN2],[NEEAN3],[NEEAN4],[NEEAN5],[NEEAN6],  [NEEAN7],[NEEAN8],[NEEAN9])
) p1
where GFCUS=094328 
GROUP BY GFCUS

But all I get is 9 NULL columns and the GFCUS value.

Where am I going wrong or missing something please?

Upvotes: 1

Views: 65

Answers (1)

Taryn
Taryn

Reputation: 247650

The problem is with your RNEEAN names and then the column names you are using in the PIVOT.

The RNEEAN column contains data that starts with NEAN but then you are attempting to pivot values with a name of NEEAN - if the column names don't match then you will return null.

Using your data above, you'd have to use:

select GFCUS, SCSAC, 
  NEAN1, NEAN2, NEAN3, NEAN4, NEAN5, NEAN6, NEAN7, NEAN8, NEAN9
from
(
  select RNEEAN, NEEAN, GFCUS, SCSAC
  from yourtable
) d
pivot
(
  min(NEEAN)
  for RNEEAN IN (NEAN1, NEAN2, NEAN3, NEAN4, NEAN5, NEAN6, NEAN7, NEAN8, NEAN9)
) piv;

See Demo

Upvotes: 2

Related Questions