Pivoting a table

[JJ297]

I'm back again and need help pivoting a table using weeks.

This is what I have thus far:

SELECT *
FROM (SELECT doc, totovrSDW, totpndSDW, totovr, totpnd,
weekdat
FROM PendingdiarySDW) AS D PIVOT (SUM
(totovr) FOR weekdat IN ([08/14/2009], [08/21/2009], [08/28/2009])) AS
p

I would like to get a distinct doc

Divide totovr and totpnd to get the percentage and put those totals
under the correct week. I threw in those weeks but I would like to
pull the last 13 weeks out of the database (from today's date) to show
a trend. How do I write that from the information above?

This is what I would like for the table to look like

Doc 10/2/2009 10/9/2009 10/16/2009
007 9.1 10.1 11.1
008 10.2 10.3 10.4
009 8.1 9.1 10.1
010 10.6 10.8 11.4

[Plamen Ratchev]

This should give you a good start to solve the problem. The idea is to calculate the totals per week, then create rank
based on weeks and pull the first 13 ranks (since rank is in descending order you get the latest 13 weeks), and finally
pivot on the rank.

WITH Totals AS (
SELECT doc,
SUM(totovr) / NULLIF(SUM(totpnd), 0) 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], [8], [9], [10], [11], [12], [13]
FROM Ranked
PIVOT
(SUM(total) FOR rk IN ([1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11],
[12], [13])) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com

[JJ297]

Thanks that does work but how do I get the weeks to appear instead of
the numbers?

[Plamen Ratchev]

You have to use dynamic SQL to generate the column names based on the last 13 dates. The following article will give you
an idea on how to implement that:
http://pratchev.blogspot.com/2008/12/dynamic-pivoting.html

But normally this type of column header formatting should be done client side.

[JJ297]

Okay thanks going to read the article now.

[JJ297]

I'm a little bit further. This query gets the 13 weeks out of the
table that I want to use:

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + convert(varchar(10),(Weekdat),
1) --converting the weekdat

FROM PendingdiarySDW

where (dateadd(ww, -14, getdate()) < weekdat) --getting 13 weeks of
data out

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

FOR XML PATH('')

), 1, 2, '') + ']';
it gives me this:
[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]

I put your query and this one together and now I'm getting incorrect
keyword from the word 'From'

This is the entire query:

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + convert(varchar(10),(Weekdat),
1) --converting the weekdat

FROM PendingdiarySDW

where (dateadd(ww, -14, getdate()) < weekdat) --getting 13 weeks of
data out

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

FOR XML PATH('')

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

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, [@listCol],
FROM Ranked PIVOT (SUM(total) FOR rk IN (@listCol)) AS P;

The error is referring to this:

SELECT doc, [@listCol],

What am I missing?

[Plamen Ratchev]

SQL Server cannot evaluate the variable value in the SELECT list. You have to use dynamic SQL (like in the article that
I posted) to concatenate the SQL to the variable with columns (in two places, in the SELECT and in the PIVOT clauses).

[JJ297]

Almost there. I'm getting incorrect syntax near ' + N'

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + convert(varchar(10),(Weekdat),
1) --converting the weekdat

FROM PendingdiarySDW

where (dateadd(ww, -14, getdate()) < weekdat) --getting 13 weeks of
data out

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

FOR XML PATH('')

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

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

(SELECT doc, ' + @listCols +
N' FROM PendingdiarySDW) as O ' +
N' PIVOT ' +
N'(SUM(totals) FOR rk IN (' + @listCols + N')) AS P;

[Plamen Ratchev]

You did not make the query dynamic. Also, the query has errors (you define CTEs, calculate expressions, and never use
the CTEs, plus last query is incorrect).

DECLARE @sql NVARCHAR(2000);

SET @sql = N'
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)

SELECT doc,' + @listCols + N'
FROM

(SELECT doc, total, weekdat
FROM Totals) as O ' +
N' PIVOT ' +
N'(SUM(total) FOR weekdat IN (' + @listCols + N')) AS P;';

EXEC(@sql);

[JJ297]

Thanks I see my bottom query was wrong. I should have been using the
column name to get the total and I didn't need the Dense_Rank() Over
either.

Thanks for you help I will get this Pivot table stuff one day. I
even purchased the T-SQL Fundamentals 2008 book you told me to get
just couldn't get the Pivot table stuff. The book is great it has
helped me with other things.