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
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()
