SQL Query; Horizontal to Vertical

Question

I have this data in my database:

+-------------+------------+--------------------------------------+
| resource_id | rkident_id |              stringval               |
+-------------+------------+--------------------------------------+
|          21 |         15 | Hostname1                            |
|          21 |         16 | vm-143                               |
|          21 |         17 | 91345645-1E18-4C76-B119-C14E950FB086 |
|          22 |         15 | Hostname2                            |
|          22 |         16 | vm-179                               |
|          22 |         17 | 91345645-1E18-4C76-B119-C14E950FB086 |
+-------------+------------+--------------------------------------+

But I would like to display it like this:

+-------------+-------------+--------------------------------------+
| IDENTVALUE1 | IDENTVALUE2 |             IDENTVALUE3              |
+-------------+-------------+--------------------------------------+
| Hostname1   | vm-143      | 91345645-1E18-4C76-B119-C14E950FB086 |
| Hostname2   | vm-179      | 91345645-1E18-4C76-B119-C14E950FB086 |
+-------------+-------------+--------------------------------------+

Please help, thanks!

Duplicate of: http://stackoverflow.com/questions/19802361/multi-column-pivot-sql-server & http://stackoverflow.com/questions/947281/multiple-column-pivot-in-t-sql & http://stackoverflow.com/questions/18575984/pivot-a-fixed-multiple-column-table-in-sql-server — Anon, Dec 05 '13 at 19:08
Did you tag it `unpivot` because you want to use that technique, or is anything OK? — Chains, Dec 05 '13 at 19:13
It's actually a pivot, which can be accomplished with a PIVOT or manually with MAX(CASE...). — Anon, Dec 05 '13 at 19:52

score 2 · Accepted Answer · answered Dec 05 '13 at 19:01

This should do it:

SELECT
  max(CASE WHEN rkident_id = 15 THEN stringval END) IDENTVALUE1,
  max(CASE WHEN rkident_id = 16 THEN stringval END) IDENTVALUE2,
  max(CASE WHEN rkident_id = 17 THEN stringval END) IDENTVALUE3
FROM t
GROUP BY resource_id

Output:

| IDENTVALUE1 | IDENTVALUE2 |                          IDENTVALUE3 |
|-------------|-------------|--------------------------------------|
|   Hostname1 |      vm-143 | 91345645-1E18-4C76-B119-C14E950FB086 |
|   Hostname2 |      vm-179 | 91345645-1E18-4C76-B119-C14E950FB086 |

Fiddle here.

SQL Query; Horizontal to Vertical

1 Answers1