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?
CodePudding user response：
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....)