Skip to content
Advertisement

PIVOT (without aggregation?) in SQL

I am trying to put together a select statement on some data like the below using TSQL:

Table1:

Property Detail Record No
Surname Smith 1
First Name Anne 1
Title Mrs 1
Gender F 1
Surname Jones 2
First Name Ben 2
Title Mr 2
Gender M 2

Which I am hoping to get results which would appear like this:

Record No Title First Name Second Name Gender
1 Mrs Anne Smith F
2 Mr Ben Jones M

I am guessing using PIVOT would the be way to achieve this? I have tried putting a statement together for this but I have no idea what I would put for the aggregation as I do not wish to aggregate in this instance.

Does anyone know how I could do this?

Advertisement

Answer

Use a MAX function. You MUST use some kind of aggregate function on PIVOT

The syntax will be something like PIVOT(MAX(Detail) FOR Property IN (Surname, FirstName....)

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement