CODEWITHSUNDEEP
sqlsql-server
Noelle
Noelle

Reputation: 782

Simple SQL Pivot returning null

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

enter image description here

Thanks for your help

Noelle

Upvotes: 0

Views: 51

Answers (1)

Rokuto
Rokuto

Reputation: 814

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;

Read more about PIVOT and UNPIVOT.

Upvotes: 1

Related Questions