question

Here's a non-dynamic query that might do what you want: /* Just setting up a temporary table and some data for testing */ DECLARE @ProdData TABLE ( Product_ID INT, Reg1 VARCHAR(20), Reg2 VARCHAR(20), Reg3 VARCHAR(20), Reg4 VARCHAR(20), Reg5 VARCHAR(20), Reg6 VARCHAR(20), Reg7 VARCHAR(20), Reg8 VARCHAR(20), Reg9 VARCHAR(20) ); INSERT INTO @ProdData (Product_ID, Reg1, Reg3, Reg6, Reg7, Reg9) VALUES (123456, '3A', '8u', '7y', 'P9', 'yu'); INSERT INTO @ProdData (Product_ID, Reg2, Reg4, Reg8) VALUES (987, 'Num2','Num4','Num8'); SELECT * FROM @ProdData; /* And here's where we do stuff. */ WITH Unpivoted AS (SELECT Product_ID, Reg, Val, ROW_NUMBER() OVER (PARTITION BY Product_ID ORDER BY Reg) AS RowNum FROM ( SELECT Product_ID, Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8, Reg9 FROM @ProdData ) p UNPIVOT(Val FOR Reg IN(Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8, Reg9)) AS unp) SELECT Product_ID, MAX(CASE WHEN RowNum = 1 THEN Val ELSE NULL END) AS Reg1, MAX(CASE WHEN RowNum = 2 THEN Val ELSE NULL END) AS Reg2, MAX(CASE WHEN RowNum = 3 THEN Val ELSE NULL END) AS Reg3, MAX(CASE WHEN RowNum = 4 THEN Val ELSE NULL END) AS Reg4, MAX(CASE WHEN RowNum = 5 THEN Val ELSE NULL END) AS Reg5, MAX(CASE WHEN RowNum = 6 THEN Val ELSE NULL END) AS Reg6, MAX(CASE WHEN RowNum = 7 THEN Val ELSE NULL END) AS Reg7, MAX(CASE WHEN RowNum = 8 THEN Val ELSE NULL END) AS Reg8, MAX(CASE WHEN RowNum = 9 THEN Val ELSE NULL END) AS Reg9 FROM Unpivoted Group by Product_ID;