Here below code, which describes to create Dynamic Pivot view from the Table.
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)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted
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
FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select * into aNewtable
from mycte;'
--print @sql
exec sp_executesql @sql;
select * from aNewtable
select * from yourtable
drop table yourtable
Happy coding!!!!!!!!!!!!!!!!!!
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)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted
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
FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select * into aNewtable
from mycte;'
--print @sql
exec sp_executesql @sql;
select * from aNewtable
select * from yourtable
drop table yourtable
Happy coding!!!!!!!!!!!!!!!!!!
0 comments:
Post a Comment