How do I Convert a SQL Script into a View or Function in order to Extract Data

[DonaldDD]

Is it possible to convert the following script into a view. Ultimately i would like to draw data using excel.
The Script goes as follows:

DECLARE @columns VARCHAR(2000)
SELECT
@columns =
COALESCE
(
@columns + ',[' + cast(DATEPART(yyyy, tr_date) as varchar) + cast(DATEPART(mm, tr_date) as varchar) + ']',
'[' + cast(DATEPART(yyyy, tr_date) as varchar)+ cast(DATEPART(mm, tr_date) as varchar) + ']'
)
FROM
audit
GROUP BY 
DATEPART(yyyy, tr_date),DATEPART(mm, tr_date)
ORDER BY DATEPART(yyyy, tr_date),DATEPART(mm, tr_date)
 
 
 
DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
SELECT
stock_code,
cast(DATEPART(yyyy, tr_date) as varchar)+ cast(DATEPART(mm, tr_date) as varchar) as trdate,
qty
FROM
audit
) PIV
PIVOT
(
SUM(qty) FOR trdate in (' + @columns + ')
) AS chld'
EXECUTE (@query)
GO

for the sake of my question the output is not important but the result is.

[Rabbit]

You can't convert that to a view. Other than the data might come from Excel, I don't see what bearing it has on pivot tables.

[ck9663]

If you really need to, just create the view every time you need to run your query. Make it a dynamic create statement so you don't manually create it every time.

Happy Coding!!!


~~ CK