CODEWITHSUNDEEP
sqlsql-serverpivot
Pixelstiltskin
Pixelstiltskin

Reputation: 332

Row values as column headings

How can I make the following sql result:

enter image description here

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

Upvotes: 0

Views: 53

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Assuming your source data actually looks like this:

enter image description here

Static Pivot

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

Returns

enter image description here

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

enter image description here

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

Upvotes: 1

Related Questions