I was trying to do dynamic columns pivot for a table. I was able to do that, thanks to the explanation provided here (SQL Server dynamic PIVOT query?)
create table pivottest (Columnname varchar(100), value varchar(100)) ;
insert into pivottest values ('Age', '25'),
('Email', 'Rob@gmail.com'),
('Phone', '888888888'),
('Name', 'Rob'),
('Age', '20'),
('Email', 'Bob@gmail.com'),
('Phone', '999999999'),
('Name', 'Bob'),
('Age', '20'),
('Name', 'Ben'),
(null, null)
I am doing the same way based on the answers provided in the link?
select *, ROW_NUMBER() over (partition by columnname order by value) Rownum
into #temp from pivottest
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Columnname)
FROM #temp c
FOR XML PATH(''))
,1,1,'')
set @query = 'SELECT '+@cols+' from
(
select Columnname, Rownum
, value
from #temp
) x
pivot
(
max(Value)
for Columnname in (' + @cols + ')
) p '
Execute @query
I get the result which I desire for pivot section, now I wanted to store the data into some temp table and refer that in the same session as it gives me error saying object does not exist.
I can do select * into some physical table in this way. I can refer the physical table every time. But I wanted to do in #temp table
set @query = 'SELECT '+@cols+' into dynamicpivotdata from
(
select Columnname, Rownum
, value
from #temp
) x
pivot
(
max(Value)
for Columnname in (' + @cols + ')
) p '
execute(@query)
select * from dynamicpivotdata
Also, I can get the results from Temp table but I have to create the table first with all the columns required, and then I can refer it in this way. But I wanted to do like Select * into for temp table like I did for physical table without specifying the column names. Is there any way to store the data in temp table?
create table #temp5 (Age int ,
Email varchar(100),
Name varchar(100),
Phone varchar(15))
set @query = 'SELECT '+@cols+' from
(
select Columnname, Rownum
, value
from #temp
) x
pivot
(
max(Value)
for Columnname in (' + @cols + ')
) p '
insert into #temp5 execute(@query)
select * from #temp5
My Final Output which I get from #temp5 and dynamicpivotdata looks like this. It would be helpful if I get the same output from select * into #temp table option if possible.
Age Email Name Phone
20 Bob@gmail.com Ben 888888888
20 Rob@gmail.com Bob 999999999
25 NULL Rob NULL
