SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Columns to Rows and a Sum
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/02/2013 :  04:43:28  Show Profile  Reply with Quote
do you mean you need to remove them from Pivot columns also? then you need to do change to both places


DECLARE @DateList varchar(5000),@MaxDate varchar(11),@EndDate varchar(11)

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112),
@EndDate = CONVERT(varchar(11),DATEADD(dd,-30,MAX(DATECOMPLETE)),112)
FROM E301836.[Log-TierMeeting]

DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] 
WHERE DATECOMPLETE > @EndDate  AND DATECOMPLETE <= @MaxDate ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')



SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL  AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >''' + @EndDate + ''' AND DATECOMPLETE <= ''' + @MaxDate + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 12/02/2013 :  04:55:10  Show Profile  Reply with Quote
Yes!!! ... that works !!! looks great .... I will validate the values in the columns , but think we are there !! big big smile :) .. thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/02/2013 :  07:27:03  Show Profile  Reply with Quote
good..glad that I could help it out for you :)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000