sql server - SQL Pivot with unbalanced data -

i'm deeling pivot function if possible "fill up" data not included in main table. table includes these data:

create table tmpdata (objid int, colid varchar(5), value varchar(50)); insert tmpdata (objid, colid, value) values(21, 'col1', 'a value'); insert tmpdata (objid, colid, value) values(21, 'col2', 'col2_1'); insert tmpdata (objid, colid, value) values(21, 'col2', 'col2_2_x'); -- second 'value' col_2 insert tmpdata (objid, colid, value) values(21, 'col3', 'col3_1'); insert tmpdata (objid, colid, value) values(22, 'col1', 'another value'); insert tmpdata (objid, colid, value) values(22, 'col2', 'col2_2'); insert tmpdata (objid, colid, value) values(22, 'col3', 'col3_2'); 

with pivot function

select * ( select   objid , colid , value tmpdata)  t pivot (max(value) colid in ([col1], [col2], [col3])) pivottable; 

i 1 (max) value objid=21 in col2:

objid col1          col2         col3 21   value        col2_2_x     col3_1 22   value  col2_2       col3_2 

what values , filled non given data objid=21 in col1 , col3:

objid col1          col2        col3 21    value       col2_2      col3_1 21    value       col2_2_x    col3_1 22    value col2_2      col3_2 

is possible pivot function or in way? many in advance joerg

you seem (sort of) want lists in columns. if can live result:

objid col1          col2        col3 21    value       col2_2      col3_1 21    null          col2_2_x    null 22    value col2_2      col3_2 

then can enumerating values:

select objid,        max(case when colid = 'col1' value end) col1,        max(case when colid = 'col2' value end) col2,        max(case when colid = 'col3' value end) col3 (select d.*,              dense_rank() on (partition objid, colid order (select null)) seqnum       tmpdata d.*      ) t group objid, seqnum; 

in sql server 2012+, can want using cumulative max():

select objid,        max(max(case when colid = 'col1' value end)) on (partition objid order seqnum) col1,        max(max(case when colid = 'col2' value end)) on (partition objid order seqnum) col2,        max(max(case when colid = 'col3' value end)) on (partition objid order seqnum) col3 (select d.*,              dense_rank() on (partition objid, colid order (select value)) seqnum       tmpdata d.*      ) t group objid, seqnum; 

note order by in dense_rank() has been changed explicitly order value.