how to extract a view from dynamic pivot

[BuilderSpec]

Hi all

i have the following code that creates a pivot table result.

declare @cols VARCHAR(MAX);
declare @qry varchar(max);
set @cols = 'North,South'
select @cols = STUFF((SELECT distinct(',' + QUOTENAME(convert(char(10),bookingdate,121)))
from pickwalk where convert(char(10),bookingdate,121)>convert(char(10),getdate(),121)

FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @cols;
set @qry = '
select * from
(
SELECT DISTINCT dbo.WalkItems.ItemCode, sbr.itemdata.description,SUM(dbo.WalkItems.Quantity) AS Total, convert(char(10),dbo.PickWalk.BookingDate,121) as dt
FROM dbo.WalkItems INNER JOIN
dbo.PickWalk ON dbo.WalkItems.WalkID = dbo.PickWalk.ID
inner join sbr.itemdata
on dbo.walkitems.itemcode = sbr.itemdata.productcode
where itemcode is not null and coalesce(quantity,0) > 0
and convert(char(10),bookingdate,121)>convert(char(10),getdate(),121)
GROUP BY dbo.WalkItems.WalkID, dbo.WalkItems.ItemCode, sbr.itemdata.description, dbo.PickWalk.BookingDate
) ps
pivot(
sum(Total) for dt in (' + @cols + ')
) as pvt order by itemcode';

-- set @qry = 'select * from salesdata';
execute (@qry);

What I want it to create this as a view so i can use : select * from viewName to get the results. However the code won't store as a view.

Any ideas greatly appreciated ?
Builderspec






Hope this helps!

Regards

BuilderSpec

 

[gk53]

you can try to create user defined function and use function as view