MS SQL.Transform rows into columns

Question

I have a simple table. As a result I need to get names of numeric columns and paste like rows and then paste its values like columns.Here is an example of table:

As a result I need to receive something like this:

I tried to get the result with PIVOT, but I have not the correct answer.

select * from (
select col1, 'val' + cast(row_number()over(partition by col1 order by col1) as nvarchar(20)) ColVal
from mytbl
    ) tmp 

pivot (
    min(col1) for ColVal in (val1,val2)
    ) pvt

Its doesn't matter. number of columns depends from number of rows. — Andriy, Jul 16 '19 at 08:44
@Andrey: Check [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — huMpty duMpty, Jul 16 '19 at 08:51

score 1 · Accepted Answer · answered Jul 16 '19 at 11:56

In this case you need to unpivot first, then pivot back:

DROP TABLE IF EXISTS dbo.temp
DROP TABLE IF EXISTS dbo.temp2

CREATE table dbo.temp(col1 INT, col2 INT, col3 INT);

INSERT INTO temp VALUES (27,93,80),(32,84,72),(46,68,75),(38,79,73),(23,77,84);

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

-- first unpivot to key value pairs
select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'temp' and
                 C.column_name like 'col%'
           for xml path('')), 1, 1, '')

set @query 
  = 'SELECT ''val'' + convert(varchar, i) as id, 
        name,
        val
     INTO dbo.temp2
     FROM 
     (
        SELECT *, row_number() over (order by col1) as i
        from temp
     ) a
     UNPIVOT
     (
        val
        FOR name IN ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

-- now pivot back
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(id)
  FROM (SELECT DISTINCT id FROM dbo.temp2) AS x;
SET @query = N'
SELECT name, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT id, name, val
  from temp2
) AS j
PIVOT
(
  SUM(val) FOR id IN ('
  + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
  + ')
) AS p;';
EXEC sp_executesql @query;

I adapted two separate scripts I had lying around, hence the two parts and the intermediate temp2 table. You can probably mash both together with a bit of elbow grease, but this should get you most of the way there.

Also adding the id (to get val1, val2 etc) dynamically means the results are sorted by col1 (val1 will have the lowest col1) but you were doing something similar in your attempt so I assume this is ok. If not, you will need to add an identity column to the data first and use that in place of the row_number()

MS SQL.Transform rows into columns

1 Answers1