SQL Pivot returns null values when pivoting (n)varchar data

Question

I'm struggling to pivot data in Microsoft SQL Server 2012. When I try to perform a pivot with varchar values, the script returns NULL values for all records. My code looks like this:

select [Identifier], [Geography Description], [Temperature Description], [Humidity Description], [Pressure Description]
from 
    (
        select [Identifier], [AttributeID], [AttributeValue]
            from [dbo].[StringAttributeValues]
    ) as A

PIVOT

(
    max(AttributeID)
    for [AttributeValue] in ([Geography Description], [Temperature Description], [Humidity Description], [Pressure Description])
) as B

I then followed the advice of the top most answer of this thread and modified my script. The end result was that my script still returns NULL values for every record across all columns.

select [Identifier], [1] as [Geography Description], [2] as [Temperature Description], [3] as [Humidity Description], [4] as [Pressure Description]
from 
    (
        select [Identifier], [AttributeID], [AttributeValue]
            from [dbo].[StringAttributeValues]
    ) as A

PIVOT

(
    max(AttributeID)
    for [AttributeValue] in ([1], [2], [3], [4])
) as B

Here is some code detailing my table structure and some sample values to work with:

    create table [dbo].[StringAttributeValues]
(
    [Identifier] int
    ,[AttributeID] int
    ,[AttributeValue] nvarchar(15)
)

insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (1, 1, 'Mountains')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (2, 1, 'Coast')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (3, 1, 'Desert')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (4, 1, 'Jungle')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (5, 1, 'Plains')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (1, 2, 'Cold')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (2, 2, 'Warm')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (3, 2, 'Hot')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (4, 2, 'Hot')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (5, 2, 'Hot')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (1, 3, 'Dry')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (2, 3, 'Humid')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (3, 3, 'Dry')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (4, 3, 'Humid')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (5, 3, 'Dry')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (1, 4, 'High')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (2, 4, 'Low')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (3, 4, 'High')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (4, 4, 'Low')
insert into dbo.StringAttributeValues ([Identifier], [AttributeID], [AttributeValue]) values (5, 4, 'Low')

Can anyone please provide me with some insight as to why this is happening and how I fix it?

(It's a pretty simple procedure to right in languages like MATLAB, C#, Python, etc)

Thank you in advance.

score 0 · Answer 1 · edited Mar 05 '16 at 12:48

0

It's just a misplacement of columns in the PIVOT clause. Try this:

SELECT [Identifier], [1] as [Geography], [2] as [Temperature], [3] as [Humidity], [4] as [Pressure]
FROM
    (
        SELECT [Identifier], [AttributeID], [AttributeValue]
        FROM dbo.StringAttributeValues
    ) as A

PIVOT
(
    max(AttributeValue)
    for AttributeID in ([1], [2], [3], [4])
) as B

edited Mar 05 '16 at 12:48

planetmaker

5,884
3
28
37

answered Mar 05 '16 at 12:10

user2533407

1

Mihai · Accepted Answer · 2015-01-29T18:01:24.463

-1

select [Identifier], [1] as [Geography Description], [2] as [Temperature Description], [3] as [Humidity Description], [4] as [Pressure Description]
from 
    (
        select [Identifier], [AttributeID], [AttributeValue]

            from [dbo].[StringAttributeValues]
    ) as x

PIVOT

(
    max([AttributeValue])
    for [AttributeID] in ([1], [2], [3], [4])
) as piv

FIDDLE

edited Jan 29 '15 at 18:01

answered Jan 29 '15 at 17:45

Mihai

26,325
7
66
81

Not my DV but the `row_number()` isn't doing anything here. The issue with their query is they had a syntax issue with their query which was causing the null. – Taryn Jan 29 '15 at 17:53
1

You've added `col` but you aren't actually using it for anything, it can be removed - http://sqlfiddle.com/#!6/231aa/4. They have everything they need to solve the problem in their data, the problem with their query is they had the `attributeId` and `attributevalue` reversed. – Taryn Jan 29 '15 at 17:59

SQL Pivot returns null values when pivoting (n)varchar data

2 Answers2