Simple SQL Pivot returning null

Question

I am having one of those days.

I am trying to pivot the data below into columns

  ID | SplitString
-------------------
|  1 | ABC 
|  2 | ABC03082017
|  3 | 03082017
|  4 | 1
|  5 | TestData

So far I have the code below but when I run it, it is returning nulls, the columns have the correct header but the data is all null.

    select * 
from 
(
    select ID,splitData from dbo.fn_splitstring(@RawData_Header, '|')
) src
pivot
(
    MAX(ID) for splitData in ([Identifier], [ProviderCode], [FileDate],[Code],[FileName])
) piv;

The first part of the pivot script is working correctly and returning the table above.

EDIT**

I am trying to return the data similar to the image below

Thanks for your help

Noelle

This is returning NULL because there are no rows with values [Identifier], [ProviderCode], [FileDate],[Code],[FileName] in column splitData. What are you trying to achive? — Rokuto
– Rokuto, Commented Aug 3, 2017 at 11:47
@Kannan I have added an image of the desired results. Thanks — Noelle
– Noelle, Commented Aug 3, 2017 at 11:55
@Rokuto I thought the line "splitData in ([Identifier], [ProviderCode], [FileDate],[Code],[FileName])" was to give the column names for the pivoted data? — Noelle
– Noelle, Commented Aug 3, 2017 at 11:59

Rokuto · Accepted Answer · 2017-08-03 12:03:03Z

1

If you want to use PIVOT, you have to change your code to:

 select [1] AS [Identifier], [2] AS [ProviderCode], [3] AS [FileDate], [4] AS [Code], [5] AS [FileName]
from 
(
    select ID,splitData from dbo.fn_splitstring(@RawData_Header, '|')
) src
pivot
(
    MAX(splitData) for Id in ([1],[2],[3],[4],[5])
) piv;

1 Comment

Noelle Over a year ago

Thank you very much for your help and the links. That worked perfectly. I have much more complex pivots to do as part of this functionality the links will be a great help.

Collectives™ on Stack Overflow

Simple SQL Pivot returning null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related