pivot - unknown periods - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pivot - unknown periods

Author

Topic

ecivgamer
Starting Member

7 Posts

Posted - 2012-08-13 : 03:20:11

Hi all,

I've got working pivot query, it works for known periods:

SELECT * FROM 
(SELECT     TOP (6) contractid, accountingDate2, restAmount2 FROM          
(SELECT     StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) 
AS accountingDate2, SUM(restAmount) AS restAmount2, COUNT(1) AS cnt
FROM         vpvbkiR_Record AS t
WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)) AS k
ORDER BY accountingDate2 DESC) AS h PIVOT (sum(restAmount2) 
FOR accountingDate2 IN ([201206], [201205], [201204], [201203], [201202], [201201])) AS S

Now how do I perform the same if I know only the quantity of periods, but don't know exact period values (without dynamic sql if possible) ?

ecivgamer
Starting Member

7 Posts

Posted - 2012-08-13 : 05:18:46

As far as I can understand, I need something like pipelined function (as it is used in oracle). Could someone please show me correct syntax for doing that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:01:53

you should use dynamic sql with PIVOT
see
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources