SQL query to output table in a grid

Question

I have a such table:

| head | attribute | value |
| h1   | attr1     | val1  |
| h1   | attr2     | val2  |
| h1   | attr3     | val3  |
| h2   | attr1     | val1  |
| h2   | attr2     | val2  |
| h2   | attr3     | val3  |
| h3   | attr1     | val1  |
| h3   | attr2     | val2  |
| h3   | attr3     | val3  |

And i need to render it in this way:

| head | attr1 | attr2 | attr3 |
| h1   | val1  | val2  | val3  |
| h2   | val1  | val2  | val3  |
| h3   | val1  | val2  | val3  |

What is the best solution for that? I can order all records by head and parse from code to render. But is that possible to group it from SQL?

what you are looking is a Pivot Table. There are tons of questions answered here. Look at it. — Jorge Campos, Mar 02 '15 at 23:26
See answer to this: http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function — Kevin, Mar 02 '15 at 23:36
The answer is NOT to store data like attribute/value pairs. It will only cause you problems. — jarlh, Mar 03 '15 at 07:59

score 2 · Answer 1 · answered Mar 02 '15 at 23:32

declare @t table (head nchar(2), attribute nchar(5),value nchar(4))

insert @t
values ('h1','attr1','val1')
,( 'h1','attr2','val2')
,( 'h1','attr3','val3')
,( 'h2','attr1','val1')
,( 'h2','attr2','val2')
,( 'h2','attr3','val3')
,( 'h3','attr1','val1')
,( 'h3','attr2','val2')
,( 'h3','attr3','val3')

select head,[attr1],[attr2],[attr3]
from @t
pivot (max(value) for attribute in ([attr1],[attr2],[attr3])) p

SQL query to output table in a grid

1 Answers1