sql server - SQL query unknown rows into columns -

i asked question , marked duplicate of how pivot unknown number of columns & no aggregate in sql server?, answer doesn't me.

i have table of data looks this, unknown number of rows , values.

recid     name      value 1         color     red 2         size      small 3         weight    20lbs 4         shape     square 

i need query return data this, building out column each row. cannot hard code except column headers 'name' , 'value'.

color     size     weight     shape red       small    20lbs      square 

here have far partly working:

insert @table values (1,'color' ,'red'), (2,'size'  ,'small'), (3,'weight','20lbs'), (4,'shape' ,'square')   ;with mycte   ( select rn,cols,val   (select  row_number() over(order name) rn, name, value          @table) src1 unpivot (val cols in ( [name], [value])) unpvt  )  select *   (select rn,cols,val           mycte) src2 pivot ( max(val) rn in ([1], [2], [3])) pvt 

which returns:

cols    1   2   3 name    color   shape   size value   red square  small 

two problems can't seem resolve.

1. i don't need column headers , first column has cols, name, value in it.
2. can't figure out how have build column each row without specifying [x] identifiers.

any guidance great i've been stuck on while now.

declare @collist nvarchar(max) set @collist = stuff((select distinct ',' + quotename(name)              #t -- table here             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  declare @q nvarchar(max) set @q = ' select *  (     select rn, name, value         (         select *, row_number() on (partition name order recid desc) rn         #t -- table here     ) x ) source pivot (     max(value)     name in (' + @collist + ') ) pvt '  exec (@q)