Dynamic SQL

External Since: Aug 20, 2008 Posts: 672

Best would be to add the rank to the select list of the final pivot query:

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N',ROW_NUMBER() OVER(ORDER BY sort) AS rn ' +
N',RANK() OVER(ORDER BY sort) AS rk ' +
N'FROM (SELECT sort, reg, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS DECIMAL(5,
2)) AS perc ' +
N'FROM #temp1) AS F ' +
N'PIVOT ' +
N'(MAX(perc) FOR year_month IN (' + @pivot_cols + ')) AS P ' +
N'ORDER BY sort;';

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

External Since: Feb 01, 2008 Posts: 51

On Dec 10, 2:53 pm, JJ297 wrote:
> On Dec 10, 2:37 pm, Plamen Ratchev wrote:
>
>
>
>
>
> > Best would be to add the rank to the select list of the final pivot query:
>
> > DECLARE @pivot_query NVARCHAR(2000);
> > SET @pivot_query =
> > N'SELECT sort, reg, ' + @pivot_cols +
> > N',ROW_NUMBER() OVER(ORDER BY sort) AS rn ' +
> > N',RANK() OVER(ORDER BY sort) AS rk ' +
> > N'FROM (SELECT sort, reg, ' +
> > N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 6),
> > 6) AS year_month, ' +
> > N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS DECIMAL(5,
> > 2)) AS perc ' +
> > N'FROM #temp1) AS F ' +
> > N'PIVOT ' +
> > N'(MAX(perc) FOR year_month IN (' + @pivot_cols + ')) AS P ' +
> > N'ORDER BY sort;';
>
> > --
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Okay got it but i had to change the order by Reg instead of using Sort
> now it's coming out right. Can't wait to get that book. Thanks again!- Hide quoted text -
>
> - Show quoted text -

Back again I realized this ranking order is for the entire row. Is
there a way I can rank each month? So in between each month would be
the rank for that month. Is that possible with this data?

DECLARE @pivot_cols NVARCHAR(1000);
SELECT @pivot_cols =
STUFF((SELECT '],[' + year_month
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME),
6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS
DATETIME), 126)
FROM #tempCnt) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 2, '') + ']';

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N',ROW_NUMBER() OVER(ORDER BY Sort) AS rn ' +
N',RANK() OVER(ORDER BY Reg) AS rk ' +
N'FROM (SELECT sort, reg, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),
6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS DECIMAL
(5,
2)) AS perc ' +
N'FROM #tempCnt) AS F ' +
N'PIVOT ' +
N'(MAX(perc) FOR year_month IN (' + @pivot_cols + ')) AS P ' +
N'ORDER BY Sort;';

EXEC(@pivot_query);

Sort Reg Nov08 rk Dec08 rk Jan09 rk Feb09 Mar09
Apr09 May09 Jun09 July09
1 NAT 11.04 12.05 14.66 14.10 13.98 14.57 15.02 15.06 16.40
A BOS 8.94 10.37 11.88 11.68 11.83 12.08 11.88 12.52 13.55
B NYC 8.28 11.58 15.38 15.51 14.81 15.27 16.14 16.65 17.68
C PHI 11.96 13.66 15.46 13.91 14.05 14.74 15.79 16.75 18.19
D ATL 10.93 11.13 14.40 13.72 14.44 14.97 15.07 15.15 16.34
E CHI 10.57 12.10 14.34 13.65 13.56 14.06 14.38 14.18 15.76
G KCM 8.03 8.56 10.09 10.91 10.63 11.55 11.35 11.22 12.62
H DAL 10.03 10.32 12.49 12.22 11.81 12.41 12.95 12.98 13.81
I DEN 12.70 13.17 16.96 15.38 15.24 16.76 17.51 17.29 20.64
J SFO 14.38 13.86 17.44 17.24 15.84 16.67 17.52 16.63 18.54
K SEA 14.93 16.52 19.80 17.18 18.01 18.23 18.22 18.53 19.64