CODEWITHSUNDEEP
sql-servert-sql
user91
user91

Reputation: 29

PIVOT not working as expected - one pivoted value per row

I am reusing PIVOT code that has worked before, and for some reason it isn't working in this case. Here is (1) a mockup of my data, (2) what I want, (3) my query, and (4) what I actually get.

1 - Mockup of data

ID Row Attribute Textcontent
600 1 Name Tom
600 1 Age 32
600 1 Type Secondary
600 2 Name Joanne
600 2 Age 36
600 2 Type Primary
601 1 Name Bob
601 1 Age 16
601 1 Type Primary
601 2 Name Alex
601 2 Age 99
601 2 Type Secondary

2 - What I want

ID Row Name Age Type
600 1 Tom 32 Secondary
600 2 Joanne 36 Primary
601 1 Bob 16 Primary
601 2 Alex 99 Secondary

3 - My query

SELECT
    [ID],
    [Row],
    [Name],
    Age,
    [Type]
FROM my_data_source
PIVOT (max(Textcontent) FOR Attribute in (
    [Name],
    Age,
    [Type]
)) as p
order by ID, [Row]

4 - What I get

ID Row Name Age Type
600 1 Tom NULL NULL
600 1 NULL 32 NULL
600 1 NULL NULL Secondary
600 2 Joanne NULL NULL
600 2 NULL 36 NULL
600 2 NULL NULL Primary
... ... ... ... ...

I am using Microsoft SQL Server. I have made PIVOT queries work before - I copied working code to create this one - so I'm a little lost as to why it's doing this.

Here's version information:

Component Name Versions
SQL Server Management Studio 15.0.18424.0
SQL Server Management Objects (SMO) 16.100.47021.0
Microsoft Analysis Services Client Tools 15.0.19750.0
Microsoft Data Access Components (MDAC) 10.0.19041.5486
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.19045

Upvotes: 1

Views: 94

Answers (1)

ValNik
ValNik

Reputation: 5916

Not as an answer. More like a curious example.
Perhaps you need to follow the PIVOT syntax more precisely, without relying on data.
Please see simple example.

With

         row_number()over(partition by ID) Row

output is not desired, as shown in question.

And with

         row_number()over(partition by ID,Attribute order by Id)Row

output is Ok.

ID Row Attribute Textcontent
600 1 Name Tom
600 2 Age 32
600 3 Type Secondary
600 4 Name Joanne
600 5 Age 36
600 6 Type Primary
601 7 Name Bob
601 8 Age 16
601 9 Type Primary
601 10 Name Alex
601 11 Age 99
601 12 Type Secondary 
SELECT [ID], [Row], [Name], Age, [Type]
FROM my_data_source
PIVOT (max(Textcontent) FOR Attribute in ( [Name], Age,[Type])
) as p
order by ID, [Row]
ID Row Name Age Type
600 1 Tom null null
600 2 null 32 null
600 3 null null Secondary
600 4 Joanne null null
600 5 null 36 null
600 6 null null Primary
601 10 Alex null null
601 11 null 99 null
601 12 null null Secondary
601 7 Bob null null
601 8 null 16 null
601 9 null null Primary

fiddle

Upvotes: 0

Related Questions