CODEWITHSUNDEEP
sqlsql-serverpivot
Amy
Amy

Reputation: 641

SQL PIVOT with varchar datatype returning null

I am trying to pivot data in a sql table so that I only get one row for each record id. However the code I am running keeps returning null values as my end results.

The table I have looks like:

"Sequence"  "University Issuing Body"**    
"1" "College1"    
"1" "College2"
"2" "College1"    
"2" "College1"    
"2" "College2"

What I need the table to look like is as follows:

"Sequence"  "University Issuing Body1"  "University Issuing Body2"  "University Issuing Body3"    
"1" "College1"  "College2"  "NULL"
"2" "College1"  "College1"  "College2"

The code I am running is as follows:

select
     Sequence]
    ,[University Issuing Body_1]
    ,[University Issuing Body_2]
    ,[University Issuing Body_3]

from (
    select
         [Sequence]
        ,[University Issuing Body]
        ,'University Issuing Body' + cast(row_number() over(partition by [Sequence] order by [University Issuing Body]) as varchar(12)) as hdg
    from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
    ) d
    pivot(
        max([University Issuing Body])
        for hdg in ([University Issuing Body_1], [University Issuing Body_2], [University Issuing Body_3])
        )pvt

However when I run the above code all my values appear as null and I get the following results:

"Sequence"  "University Issuing Body_1" "University Issuing Body_2" "University Issuing Body_3"

"1" "NULL"  "NULL"  "NULL"

"2" "NULL"  "NULL"  "NULL"

Any help would be really appreciated as I have been searching for a solution online and can't seem to find where I am going wrong.

Thanks in advance.

Upvotes: 0

Views: 241

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Just use conditional aggregation:

select Sequence,
       max(case when seqnum = 1 then [University Issuing Body] end) as University_Issuing_Body_1,
       max(case when seqnum = 2 then [University Issuing Body] end) as University_Issuing_Body_2,
       max(case when seqnum = 3 then [University Issuing Body] end) as University_Issuing_Body_3
from (select pqj.*
             row_number() over (partition by [Sequence] order by [University Issuing Body]) as seqnum
      from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined] pqj
     ) d
group by [University Issuing Body];

If you really want to use pivot, there is no reason to define hdg:

select [Sequence],
       [1] as [University Issuing Body_1], 
       [2] as [University Issuing Body_2], 
       [3] as [University Issuing Body_3]
from (select [Sequence], [University Issuing ,Body]
             row_number() over (partition by [Sequence] order by [University Issuing Body]) as seqnum
      from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
     ) d
pivot (max([University Issuing Body])
       for seqnum in (1, 2, 3)
      )pvt

Upvotes: 1

welbeck
welbeck

Reputation: 36

You're constructing the intermediate table with hdg taking the format University Issuing Body<n>. But you're then pulling out values where hdg has the format University Issuing Body_<n>. Unsurprisingly, this doesn't match anything - so you get NULLs.

Just put an underscore in your construction of hdg:

select
 [Sequence]
,[University Issuing Body_1]
,[University Issuing Body_2]
,[University Issuing Body_3]
from (
    select
        [Sequence]
        ,[University Issuing Body]
        ,'University Issuing Body_' + cast(row_number() over(partition by [Sequence] order by [University Issuing Body]) as varchar(12)) as hdg
    from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
    ) d
    pivot(
        max([University Issuing Body])
        for hdg in ([University Issuing Body_1], [University Issuing Body_2], [University Issuing Body_3])
        ) pvt

Upvotes: 1

Related Questions