By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

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

P: 1
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:


Expand|Select|Wrap|Line Numbers
  1. DECLARE @columns VARCHAR(2000)
  2. SELECT
  3. @columns =
  4. COALESCE
  5. (
  6. @columns + ',[' + cast(DATEPART(yyyy, tr_date) as varchar) + cast(DATEPART(mm, tr_date) as varchar) + ']',
  7. '[' + cast(DATEPART(yyyy, tr_date) as varchar)+ cast(DATEPART(mm, tr_date) as varchar) + ']'
  8. )
  9. FROM
  10. audit
  11. GROUP BY 
  12. DATEPART(yyyy, tr_date),DATEPART(mm, tr_date)
  13. ORDER BY DATEPART(yyyy, tr_date),DATEPART(mm, tr_date)
  14.  
  15.  
  16.  
  17. DECLARE @query VARCHAR(8000)
  18. SET @query = 'SELECT *
  19. FROM
  20. (
  21. SELECT
  22. stock_code,
  23. cast(DATEPART(yyyy, tr_date) as varchar)+ cast(DATEPART(mm, tr_date) as varchar) as trdate,
  24. qty
  25. FROM
  26. audit
  27. ) PIV
  28. PIVOT
  29. (
  30. SUM(qty) FOR trdate in (' + @columns + ')
  31. ) AS chld'
  32. EXECUTE (@query)
  33. GO
for the sake of my question the output is not important but the result is.
Jan 31 '13 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Jan 31 '13 #2

ck9663
Expert 2.5K+
P: 2,878
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
Jan 31 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.