Last post Oct 05, 2017 11:54 AM by VitaliyMF
Sep 28, 2017 05:55 PM|SheldonS|LINK
I have created a dynamic PIVOT script and it returns all of my data properly. Is it possible to put that result set into a table or view without hard coding anything?
The source table in question may have fields added or renamed. To prevent pushing changes every time a new field is added I thought I would do the pivot dynamically. Now I cannot figure out how to create a table or view on the fly that uses this result
If it isn't possible that is fine as well. I just thought I would get a faster answer here than me burning more hours searching and trying different things.
Sep 28, 2017 06:19 PM|limno|LINK
Check out this sample:
Create table yourtable (itemID INT, part CHAR(1))
INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')
DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)
select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
Set @sql=N' if object_id(''anewtable'',''U'') is not null drop table anewtable ; with mycte as (SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select * into aNewtable
exec sp_executesql @sql;
select * from aNewtable
drop table yourtable
Sep 28, 2017 06:35 PM|SheldonS|LINK
Thank you. That example appears to do what I need. I will try to adapt it to my situation and see how it goes.
Just finished making it work for my situation. Thank you very much!
Oct 05, 2017 11:54 AM|VitaliyMF|LINK
Another alternative is using specialized OLAP library like
NReco.PivotData - it can perform data grouping by dynamic configuration (dimensions/measures). For relatively small datasets you can feed it with tabular data (say, datareader) or perform simple SELECT ... GROUP BY query and load results into PivotData
object for further processing.