From: Plamen Ratchev on
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
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
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
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