Row values as column headings

Question

How can I make the following sql result:

Be rendered in a result set like this

| ID  | Chain Size      | Length  | Hook Type  | Shortening Grab |
|-----|-----------------|---------|------------|-----------------|
| 163 | 7mm (1.5 tonne) | 1 metre | C Hook     |  Yes            |
| 226 | 7mm (1.5 tonne) | 1 metre | C Hook     |  No             |
| 247 | 7mm (1.5 tonne) | 1 metre | Latch Hook |  No             |

I know that the values in columns 2,4,6 and 8 (which I want to be headers) will be the same across all rows (but different depending on the initial query).

I believe the approach for what I want is through the use of PIVOT but really struggling to get the desired result.

Thanks

You could try a simple rename of the column you select. i.e: SELECT id AS ID, attributevalue AS Chain Size FROM table — Soutzikevich, Jun 03 '17 at 16:14
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Rachel Ambler, Jun 03 '17 at 17:17
Two or more columns can't have the same name within table. Column's name should be unique. Source table should have following columns: attributeName1, attributeValue1, attributeName2, attributeValue2, ... . — Bogdan Sahlean, Jun 03 '17 at 18:11

John Cappelletti · Accepted Answer · 2017-06-03T18:03:08.200

Assuming your source data actually looks like this:

Static Pivot

Select *
From  YourTable
Pivot (max(attributeValue) For [attributeName] in ([Chain Size],[Length],[Hook Type],[Shortening Grab]) ) p

Returns

Dynamic Approach

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([attributeName]) From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select *
From YourTable
Pivot (max(attributeValue) For [attributeName] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

Notice, without an item for column sequence, you'll see that they are alphabetical.

Row values as column headings

1 Answers1