T-SQL Pivot with dynamic columns

Question

I know there's a ton of Pivot questions, but every one I find seems to do some sort of SUM or COUNT operation, which is not what I need. I have a table like this:

id      field        val
---------------------------------
1234    type         Blade Server
1234    memory       24
1234    hostname     test_box1
4567    type         Blade Server
4567    memory       98
4567    hostname     test_box2
4567    ip           192.168.1.4

id is a foreign key that links field/val. What is in field is dynamic and not known ahead of time... it's basically a generic key/value pair store. (Which I can't change BTW... it's already set in the architecture). But I'm trying to create a pivot view that will take the above table and give me this:

id      type            memory  hostname    ip  
-------------------------------------------------------
1234    Blade Server    24      test_box1   NULL
4567    Blade Server    98      test_box2   192.168.1.4

Note that id 1234 does not have an "ip" field entry, so it needs to show up as NULL in the pivoted table. Basically, every "field" option should become a column, linked by "id". I guess this is an aggregation operation, but I couldn't figure how to make it work...

Any thoughts?

Looks like a basic dinamic pivot. Did you try use `MAX()` or `MIN()` as your aggregation function? — Juan Carlos Oropeza, Oct 14 '15 at 16:47
Although you didn't tag the question with sql-server I assumed it was as the title had t-sql in it. The question I marked as duplicate should help you. Also, see this: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — jpw, Oct 14 '15 at 16:52
@JuanCarlosOropeza Yes indeed, and that is exactly what the duplicate deals with (as well as the second question I linked to). — jpw, Oct 14 '15 at 16:57
@jpw, my bad the screen only show first half of the answer and was a static pivot. — Juan Carlos Oropeza, Oct 14 '15 at 16:58
Here is a [**Fiddle**](http://sqlfiddle.com/#!6/5ca9c7/7) of your sample working in SQL Server — Juan Carlos Oropeza, Oct 14 '15 at 17:08
Thanks @JuanCarlosOropeza , that worked! Slightly more complicated since the table I was using was actually a join of two other tables... I left that part out... but I made a temp table and it works fine with that :) — Adam Haile, Oct 14 '15 at 17:29

T-SQL Pivot with dynamic columns

0 Answers0