Dear Frnds,
I need to change rows as columns I have used this code.. this code is executed but all values comes null.. but more than 20 values are in database.
What is the mistake in this code..
please ASAP
DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ItemName +'_'+c.col)
from Vw_DailyEntry
cross apply
(
select 'MatQty' col
union all
select 'Rate'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsName
= STUFF((SELECT distinct ', ' + QUOTENAME(ItemName +'_'+c.col)
+' as ['
+ ItemName + case when c.col = 'MatQty' then ']' else ' Rate]' end
from Vw_DailyEntry
cross apply
(
select 'MatQty' col
union all
select 'Rate'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select RetDate,DPNo,
' + @colsName + '
from
(
select DPNo,RetDate,
ItemName +''_''+col col,
MatQty
from
(
select DPNo,ItemName,RetDate,
cast(MatQty as numeric(10, 2)) mtqty,
cast(Rate as numeric(10, 2)) rates
from Vw_DailyEntry
) src
unpivot
(
MatQty
for col in (mtqty,rates)
) unpiv
) s
pivot
(
Max(MatQty)
for col in (' + @cols + ')
) piv'
execute(@query)