Adding a where clause to a Pivot Query

[JJ297]

Plamen thanks again for your help with this query:

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

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

FROM DiaryPct

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

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

FOR XML PATH('')

), 1, 2, '') + ']';
DECLARE @sql NVARCHAR(2000);
SET @sql = N'
WITH Totals AS (
SELECT sort, doc, dist, area, reg, CASE
WHEN SUM(totovr)= 0 THEN 0
WHEN SUM(totpnd) = 0 THEN 0
ELSE
(convert(decimal(6,1),(((((sum(totovr))) * 1.00) / (sum(totpnd))) *
100)))
END AS total, weekdat
FROM DiaryPct
GROUP BY sort,doc, dist, area, reg, weekdat)
SELECT sort,reg, ' + @listCol +
N' ,doc, dist,area ' +
N' Into DiariesPivot ' +
N'FROM

(SELECT sort,doc, dist, area, reg,total, weekdat
FROM Totals) as O ' +
N' PIVOT ' +
N'(SUM(total) FOR weekdat IN (' + @listCol + N')) AS P;';

EXEC(@sql);

I have the results going into a table called DiariesPivot. Now I'm
trying to get certain dates out that a user will specify. I tried to
add this

N'(where weekdat = 06/26/2009');

before the EXEC (@sql); and getting:

incorrect syntax near weekdat.

What I want to get is the column 06/26/2009. Since I've pivot the
table the column names are the distinct weekdat called 06/26/2009 so I
don't know how to go about getting it out.

[Plamen Ratchev]

I am really not sure what are you trying to filter. Do you want to select only a specific column from the pivoted
output, or to filter on a date value?

If you want to filter a date value, then this will do:

N'FROM
(SELECT sort,doc, dist, area, reg,total, weekdat

FROM Totals WHERE weekdat = ''20090626'') as O ' +

N' PIVOT ' +
N'(SUM(total) FOR weekdat IN (' + @listCol + N')) AS P;';

If you need to pick a column, then you need to filter the column list:

SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + convert(varchar(10),(Weekdat),1)

FROM DiaryPct WHERE weekdat = ''20090626'' ...

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

[JJ297]

So sorry for the confusion. I realized I can't use that stored
procedure (the pivot one) because I need the column name (weekdat) to
get what I want out of the database then pivot the table.

I have a website where a user can select several weekdat's

Here's what I want the results to look like if the user selected
6/26/2009 and 7/31/2009

Sort Reg 6/26/2009 7/31/2009
1 PHI 13.0 11.5
x 01 14.9 11.5
x 02 24.6 19.8
x 03 6.7 5.6
x 04 7.4 7.7
x 05 5.4 9.2
x 06 10.3 11.6
y 199 23.7 9.0
y 200 32.6 17.6

Below is the table that I need to write a stored procedure to get the
above results.

Here's an example:

I want to do this to the columns totovr and totpnd:

SELECT sort, doc, dist, area, reg, CASE
WHEN SUM(totovr)= 0 THEN 0
WHEN SUM(totpnd) = 0 THEN 0
ELSE
(convert(decimal(6,1),(((((sum(totovr))) * 1.00) / (sum(totpnd))) *
100)))
END AS total, weekdat

Sort Reg Area
Weekdat totovr totpnd
1 PHI PH 6/26/2009 12:00:00 AM 403 3092
1 PHI PH 7/31/2009 12:00:00 AM 379 3297
1 PHI PH 8/28/2009 12:00:00 AM 303 2896
1 PHI PH 9/25/2009 12:00:00 AM 322 2909
1 PHI PH 10/30/2009 12:00:00 AM 282 2971
x 01 01 6/26/2009 12:00:00 AM 94 631
x 02 02 6/26/2009 12:00:00 AM 176 716
x 03 03 6/26/2009 12:00:00 AM 15 225
x 04 04 6/26/2009 12:00:00 AM 47 635
x 05 05 6/26/2009 12:00:00 AM 22 411
x 06 06 6/26/2009 12:00:00 AM 49 474

[JJ297]

> x       06      06      6/26/2009 12:00:00 AM          49              474- Hide quoted text -
>
> - Show quoted text -

Sorry here's the table it got messed up.

[JJ297]

I figured it out:

SELECT sort, doc, dist, area, reg, CASE WHEN SUM(totovr) = 0 THEN
0 WHEN SUM(totpnd) = 0 THEN 0 ELSE (CONVERT(decimal(6, 1), (((((SUM
(totovr))) * 1.00)
/ (SUM(totpnd))) * 100))) END AS total, weekdat
FROM DiaryPct
WHERE (weekdat = '6/26/2009') OR
(weekdat = '7/31/2009')
GROUP BY sort, doc, dist, area, reg, weekdat
ORDER BY weekdat

Now I want to pivot the table and have weekdat as a parameter to grab
as many weekdat's the user wishes to see.

I still want the dates on top of the columns don't know how to get
them out of the total column and into their separate columns. Here's
what the results look like from the stored procedure above.

Sort Reg Area Total weekdat

1 PH PHI 13.0 06/26/09
1 PH PHI 11.5 07/31/09
x 01 01 14.9 06/26/09
x 01 01 11.5 07/31/09
x 02 02 24.6 06/26/09
x 02 02 19.8 07/31/09
x 03 03 6.7 06/26/09
x 03 03 5.6 07/31/09
x 04 04 7.4 06/26/09

[Plamen Ratchev]

You need to use the exact same pivoting as before. Since it is dynamic and you already filtered the data for the two
dates it will automatically generate pivoted data with two columns for each date.

[JJ297]

But I don't understand it brings back all of the data in the table say
I want only one weekdat how would I just be able to put that in to get
that. Would I need a where clause?

[Plamen Ratchev]

You simply need to change the WHERE clause of the query to filter on only one date:

WHERE weekdat = '20090626'

Alternatively, as I noted in my first post you can do the filtering when you build the dynamic column list:

SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + convert(varchar(10),(Weekdat),1)
FROM DiaryPct WHERE weekdat = ''20090626'' ...

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

[JJ297]

Okay thanks that works I was trying to add the where clause down at
the bottom.

I have this now:

declare @weekdat datetime

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

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

FROM DiaryPctWeek
WHERE weekdat = '20090626'or weekdat='20090731'

Now my problem is I don't know which dates they want and how many. I
declared weekdat can I just do this

WHERE weekdat = @weekdat

so 1, 2, 3 (how many weekdat's they select) will appear on the page?

[Plamen Ratchev]

The best would be if you are on SQL Server 2008 to use a table-valued parameter to send the selected dates as table to
the procedure and join or use the IN predicate to filter only those dates.

If you are on prior version of SQL Server then you can see Erland's article on how to handle list format and split it to
table format:
http://www.sommarskog.se/arrays-in-sql-2005.html

Since you already use dynamic SQL for the pivot, you can also send the dates as string, like "'20090626', '20090731'"
and then concatenate the parameter to the dynamic SQL:

...
FROM DiaryPctWeek
WHERE weekdat IN (' & @weekdat & ') ...

You have to be aware that allowing user entry into dynamic SQL can expose your application to SQL injection. Erland's
article explains the issues with this method in more details.

[JJ297]

Thanks for this info. I am using 2005 so I will take a look at
Erland's article.

I might just put more code in the code behind page in .net to pull it
out there. This is a great start thanks again for your help!