Possible Duplicate:
Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
I have the following query to first pull 12 records for each of my foreign keys(null values in the case of missing records), and then turn all 12 records per foreign key into a column. The code below however, generates the 12 rows but fails to turn all the rows into columns. Instead it does the transform for just one of my foreign key IDS and then stops.
Please help me pivot this properly so that for each ProductID, I get a row. Thanks in advance.
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
--BEGIN TRY
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#ManufacturerAttributes')
)
BEGIN
DROP TABLE #ManufacturerAttributes
END
Create table #ManufacturerAttributes
(
ProductID uniqueIdentifier,
PAID uniqueidentifier,
MfgAttrLabel varchar(50),
MfgAttrVal varchar(3072),
MfgAttrUOM varchar(50),
rowindex int
)
;With Number
As
(
Select 1 as rownum union all Select 2 union all Select 3 union all Select 4 union all Select 5 union all Select 6 union all Select 7 union all Select 8 union all Select 9 union all Select 10 union all Select 11 union all Select 12
),
ProductDetail
as
(
select P.ProdID, N.rownum from IDWProduct P cross join Number N
)
Insert into #ManufacturerAttributes
Select ProdID , PAVAttributeID,PANAme, PAVValue, UOMLabel, P.rownum
from ProductDetail P
Left join (select Pr.*, row_number() over (partition by PAVProductID order by PAVID) as Rn from IDWProductAttributeValues Pr ) Pr ON rownum = Pr.Rn And PAVProductID = ProdID
left join IDWUnitofMeasures on Pr.PAVUOM = UOMID
left join IDWAttributes A on Pr.PAVAttributeID = A.PAID AND A.PAIsManufacturerSpecific = 1
Select * from #ManufacturerAttributes
select @colsUnpivot = stuff((select ','+quotename(C.name)
from tempdb.sys.columns as C
where C.object_id = object_id('tempdb..#ManufacturerAttributes')
for xml path('')), 1, 1, '')
-- select @colsUnpivot
select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rn as varchar(10)))
from
(
select row_number() over(partition by ProductID order by ProductID) rn from #ManufacturerAttributes
) t
cross apply
tempdb.sys.columns as C
where C.object_id = object_id('tempdb..#ManufacturerAttributes')
group by c.name, t.rn
order by t.rn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select @colsPivot
set @query
='select *
from
(
select ProductID, PAID, RowIndex,
col + cast(rn as varchar(10)) new_col,
val
from
(
select
Cast (ProductID as NVarchar(3072)) ProductID
,Cast(PAID as NVarchar(3072)) PAID
,Cast (MfgAttrLabel as NVarchar(3072)) MfgAttrLabel
,Cast (MfgAttrVal as NVarchar(3072)) MfgAttrVal
,Cast (MfgAttrUOM as NVarchar(3072)) MfgAttrUOM
,Cast(rowindex as NVarchar(3072)) rowindex
,row_number() over(partition by ProductID order by ProductID) rn
from #ManufacturerAttributes
) x
unpivot
(
val
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(val)
for new_col in
('+ @colspivot +')
) p'
-- Print @query
exec(@query)
