Dynamic Pivot Resluts - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic Pivot Resluts

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-24 : 01:11:57

Hi,

I am using a Dynamic columns Pivot to determine the column name and so the results.My problem is I want to order the results based on the column name, witch is a date description name (example: 'sep 2011')...The results of this pivot I want to insert into a table every month. Therefore, every month insert one row into my final table...but it is important that the specific columns witch is the date description) that are variable from my pivot result set is mapped to the final static table with the same column name?
I hope you can help and I made the problem clear...

Example:

Pivot Results=
@Col1,@col2,@col3,@col4
Name,Mar,Jan,Feb

Final Table=
Col1,col2,col3,col4
Name,Jan,Feb,Mar

Script for pivot:

DECLARE @Cols VARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + colName + ']',
'[' + colName + ']')
FROM (
SELECT DISTINCT
CONVERT(VARCHAR,datecol,103) colName
FROM ctbl_IV_DemandandForecast_Waterfall_Storage
) s
ORDER BY colName DESC

DECLARE @query VARCHAR(MAX)
SET @query = N'
SELECT * From
(SELECT daterow as DATE,datecol,qty
FROM ctbl_IV_DemandandForecast_Waterfall_Storage) p
PIVOT
(
SUM(qty) FOR datecol IN
( '+
@cols +' )
) AS pvt'

EXECUTE(@query);

Many Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources