Dynamic Pivot (row To Columns)

I have a Table1: ID Instance Name Size Tech 1 0 D1 123 ABC 1 1 D2 234 CDV 2 2 D3 234 CDV 2 3 D4 345 SDF I need the resultset using

Solution 1:

Your desired output is not exactly clear, but you can use the both the UNPIVOT and PIVOT function to get the result

If you know the number of columns, then you can hard code the values:

select*from
(
  select id, 
    'Instance'+cast(instance asvarchar(10))+'_'+col col, 
    valuefrom 
  (
    select id, 
      Instance, 
      Name, 
      cast(Size asvarchar(50)) Size,
      Tech
    from yourtable
  ) x
  unpivot
  (
    valuefor col in (Name, Size, Tech)
  ) u
) x1
pivot
(
  max(value) 
  for col in
    ([Instance0_Name], [Instance0_Size], [Instance0_Tech], 
     [Instance1_Name], [Instance1_Size], [Instance1_Tech], 
     [Instance2_Name], [Instance2_Size], [Instance2_Tech], 
     [Instance3_Name], [Instance3_Size], [Instance3_Tech])
) p

See SQL Fiddle with Demo

Then if you have an unknown number of values, you can use dynamic sql:

DECLARE@queryAS NVARCHAR(MAX),
    @colsPivotas  NVARCHAR(MAX)

select@colsPivot= STUFF((SELECT','+ quotename('Instance'+cast(instance asvarchar(10))+'_'+c.name)
                    from yourtable t
                    cross apply sys.columns as C
                    where C.object_id = object_id('yourtable') and
                         C.name notin ('id', 'instance')
                    groupby t.instance, c.name
                    orderby t.instance
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set@query='select *
      from
      (
        select id, 
          ''Instance''+cast(instance as varchar(10))+''_''+col col, 
          value
        from 
        (
          select id, 
            Instance, 
            Name, 
            cast(Size as varchar(50)) Size,
            Tech
          from yourtable
        ) x
        unpivot
        (
          value
          for col in (Name, Size, Tech)
        ) u 
      ) x1
      pivot
      (
        max(value)
        for col in ('+@colspivot+')
      ) p'exec(@query)

See SQL Fiddle with Demo

If the result is not correct, then please edit your OP and post the result that you expect from both of the Ids you provided.