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:

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?

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....)

Advertisement

Answer