Prev: LongText not supported as output parameter
Next: What does "Invalid pseudocolumn "$v"." mean?
From: Plamen Ratchev on 1 Apr 2010 21:58
You have to apply first unpivoting and then pivot the data. Try this:

SELECT col,
January, February, March, April, May, June,
July, August, September, October, November, December
FROM (
SELECT M.[month],
Actual_Month AS [Current YR Actual],
Target_month AS [Current YR Target],
Forecast_Month AS [Current YR Forecast]
FROM SPARS.dbo.t_kpi_data AS C
JOIN CSSMetrics.dbo.Months2010 AS M
ON C.YearMonth BETWEEN M.bd AND M.ed) AS T
UNPIVOT
(value FOR col IN ([Current YR Actual], [Current YR Target], [Current YR Forecast])) AS U
PIVOT
(SUM(value)
FOR [month] IN (January, February, March, April, May, June,
July, August, September, October, November, December)) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Chamark via SQLMonster.com on 4 Apr 2010 20:07
Thank you so much Plamen - this worked - so much to learn - I appreciate your
assistance - You rock!

Plamen Ratchev wrote:
>You have to apply first unpivoting and then pivot the data. Try this:
>
>SELECT col,
> January, February, March, April, May, June,
> July, August, September, October, November, December
>FROM (
>SELECT M.[month],
> Actual_Month AS [Current YR Actual],
> Target_month AS [Current YR Target],
> Forecast_Month AS [Current YR Forecast]
>FROM SPARS.dbo.t_kpi_data AS C
>JOIN CSSMetrics.dbo.Months2010 AS M
> ON C.YearMonth BETWEEN M.bd AND M.ed) AS T
>UNPIVOT
>(value FOR col IN ([Current YR Actual], [Current YR Target], [Current YR Forecast])) AS U
>PIVOT
>(SUM(value)
> FOR [month] IN (January, February, March, April, May, June,
> July, August, September, October, November, December)) AS P;
>

--
Message posted via http://www.sqlmonster.com

From: Chamark via SQLMonster.com on 13 Apr 2010 13:42
Hi Plamen,

What if I wanted to change the JOIN statement to something like
WHERE Yearmonth between '01/01/2010' and '12/31/2010' and datasource_ID =
'629'

this would eliminate the need for the CSSMetrics table to be used

Plamen Ratchev wrote:
>You have to apply first unpivoting and then pivot the data. Try this:
>
>SELECT col,
> January, February, March, April, May, June,
> July, August, September, October, November, December
>FROM (
>SELECT M.[month],
> Actual_Month AS [Current YR Actual],
> Target_month AS [Current YR Target],
> Forecast_Month AS [Current YR Forecast]
>FROM SPARS.dbo.t_kpi_data AS C
>JOIN CSSMetrics.dbo.Months2010 AS M
> ON C.YearMonth BETWEEN M.bd AND M.ed) AS T
>UNPIVOT
>(value FOR col IN ([Current YR Actual], [Current YR Target], [Current YR Forecast])) AS U
>PIVOT
>(SUM(value)
> FOR [month] IN (January, February, March, April, May, June,
> July, August, September, October, November, December)) AS P;
>

--
Message posted via http://www.sqlmonster.com

From: Plamen Ratchev on 13 Apr 2010 16:59
Yes, you can use a predicate for the dates and eliminate the JOIN.

Just a note: better to use language neutral date format like "yearmonth BETWEEN '20100101' AND '20101231'", or a
half-closed interval if the yearmonth column has time values "yearmonht >= '20100101' AND yearmonth < '20100201'".

However, in the query you also use the month column from the table you want to eliminate, and that is essential part of
the solution. You can replace it with the following expression: DATENAME(MONTH, yearmonth).

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: LongText not supported as output parameter
Next: What does "Invalid pseudocolumn "$v"." mean?