rows to columns help

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  February 22, 2013 at 9:23 am

  #268965

  CREATE TABLE [dbo].[conv_onet_update_sourceTable_test](

  [updateId] [bigint] NULL,

  [col_parent_table_name] [varchar](500) NOT NULL,

  [colName_id] [varchar](24) NULL,

  [colName] [nvarchar](max) NULL,

  [id] [varchar](24) NULL,

  [colValue] [nvarchar](max) NULL,

  ) ON [PRIMARY]

  insert into [dbo].[conv_onet_update_sourceTable_test] (updateId, col_parent_table_name, colName_id, colName, id, colValue)

  select '65','lastoccs','2','username','2','4786'

  union all

  select '65','lastoccs','1','idnum','1','1066'

  union all

  select '66','lastoccs','2','username','2','4786'

  union all

  select '66','lastoccs','1','idnum','1','1067'

  --i need this transposed. Rows to columns w/ associated values

  --OUTPUT should look LIKE the below :

  --NOTE: the order of the columns make not difference. Also, in this example u can see that there should be three columns in the output. I wont know how many columns there will be in the real problem. it needs to be dynamic

  --updateid | username | idnum

  -- 65 | 4786 | 1066

  -- 66 | 4786 | 1067

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  February 22, 2013 at 1:04 pm

  #1590499

  i got it. Really cool dynamic solution

  DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

  SET @columns = N'';

  SELECT @columns += N', p.' + QUOTENAME(colname)

  FROM (

  SELECT o.colname

  FROM [dbo].[conv_onet_update_sourceTable_test] o

  GROUP BY o.colname

  ) AS x;

  SET @sql = N'

  SELECT ' + STUFF(@columns, 1, 2, '') + '

  FROM

  (

  SELECT o.updateid,o.colname, o.colvalue

  FROM [dbo].[conv_onet_update_sourceTable_test] o

  ) AS j

  PIVOT

  (

  min(colValue) FOR colname IN ('

  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

  + ')

  ) AS p;';

  PRINT @sql;

  EXEC sp_executesql @sql;