-1

Given table:

create table test(obj varchar(99), prop varchar(99), val varchar(99));

insert into test (obj , prop , val ) values ('this', 'type A', 'foo');
insert into test (obj , prop , val ) values ('this', 'type B', 'bar');
insert into test (obj , prop , val ) values ('this', 'type C', 'asd');
insert into test (obj , prop , val ) values ('that', 'type B', 'buz');
insert into test (obj , prop , val ) values ('that', 'type A', 'qwe');

How can I select following:

| obj | type A | type B | type C|
  this  foo      bar      asd
  that  qwe      buz      NULL

This is not a duplicate of "pivot all rows to cols" questions; difference here is condition - value can go to several columns, based on type.

avj
  • 1,626
  • 1
  • 19
  • 23
  • 1
    Which dbms are you using? – jarlh Aug 25 '15 at 12:21
  • In general, any particular SQL query will always produce a result set with a particular "shape" - the number of columns, their *names* and data types are fixed. Probably have to dynamically construct the query, and the facilities for doing that vary between database systems. So please [edit] and add a suitable tag, as the [tag:sql] tag suggests. – Damien_The_Unbeliever Aug 25 '15 at 12:25
  • Edited to answer questions above. More than 2 types, MS SQL. – avj Aug 25 '15 at 12:27

1 Answers1

1

Try

select obj, [type A], [type B], [type C]
from test
pivot (max(val) for prop in ([type A], [type B], [type C])) B

SQL Fiddle

Nizam
  • 4,569
  • 3
  • 43
  • 60