Help with Pivot table - 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 2005 Forums

Transact-SQL (2005)

Help with Pivot table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-12 : 10:11:39

I am trying to dynamically pivot my table from the info in the database. I have a field called weekdat and want only 13 weeks showing up at a time from today's date.

I have two separate queries working. This one list the weeks I have in the field called weekdat:

DECLARE @listCol VARCHAR(2000)

SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' + convert(varchar(10),(Weekdat),1)

                        FROM    PendingdiarySDW

                        ORDER BY '],[' + convert(varchar(10),(weekdat),1)

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'

Print @listCol

Here's the result from this query:
[05/22/09],[05/29/09],[06/05/09],[06/12/09],[06/19/09],[06/26/09],[07/03/09],[07/10/09],[07/17/09],[07/24/09],[07/31/09],[08/07/09],[08/14/09],[08/21/09],[08/28/09],[09/04/09],[09/11/09],[09/18/09],[09/25/09],[10/02/09],[10/09/09],[10/16/09],[10/23/09],[10/30/09]

This second query works since I manually added the info. How do I get rid of the numbers [1], [2], [3], etc and have the database list the 13 weeks from the weekdat column?

WITH Totals AS (SELECT     doc, CASE WHEN sum(totovrSDW) + sum(totovr) = 0 THEN 0 WHEN sum(totpndSDW) + sum(totpnd) 
                                                           = 0 THEN 0 ELSE (CONVERT(decimal(6, 1), (((((sum(totovrSDW) + sum(totovr))) * 1.00) / (sum(totpndSDW) + sum(totpnd))) * 100))) 
                                                           END AS total, weekdat
                                    FROM         PendingdiarySDW
                                    GROUP BY doc, weekdat), Ranked AS
    (SELECT     doc, total, DENSE_RANK() OVER (ORDER BY weekdat DESC) AS rk
FROM         Totals)
    SELECT     doc, [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13]
     FROM         Ranked PIVOT (SUM(total) FOR rk IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13])) AS P;

I don't know how to get them both together to get what I want. Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-12 : 10:16:10

Almost there I've added this to the first query to get 13 weeks out of the database from today's date.

where (dateadd(ww, -13, getdate()) < weekdat)

This is the first query all together:

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + convert(varchar(10),(Weekdat),1)

FROM PendingdiarySDW

where (dateadd(ww, -13, getdate()) < weekdat)

ORDER BY '],[' + convert(varchar(10),(weekdat),1)

FOR XML PATH('')

), 1, 2, '') + ']'

Print @listCol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources