Transfering a Access Pivot query to SQL Server 2005

[Chance1234]

have read the pivot operator on bol and a few examples on the web. Unfortunatly dont have any of the sample databases, so can't actually see the pivot command working.

I am trying to convert this Access query to SQL

TRANSFORM Max(dbo_v_kpi_front_page.indicator_results_result) AS MaxOfindicator_results_result
SELECT dbo_v_kpi_front_page.domain_year, dbo_v_kpi_front_page.heading
FROM dbo_v_kpi_front_page
GROUP BY dbo_v_kpi_front_page.domain_year,       dbo_v_kpi_front_page.heading
PIVOT dbo_v_kpi_front_page.domain_quarter

The field domain_quarter contains Q1, Q2, etc which is what i want going across the top.

Ive played around with

SELECT 
   domain_quarter, 
   Q1 = ISNULL([Q1], 0), 
   Q2 = ISNULL([Q2], 0), 
   Q3 = ISNULL([Q3], 0), 
   Q4 = ISNULL([Q4], 0)
FROM 
   v_kpi_front_page
PIVOT ( 
   SUM(indicator_results_result)
   FOR domain_quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS Indicator

But could really do with some pointers where I am going wrong.





Chance,

Filmmaker, gentleman and polla stilo eleous

 

[rac2]

Why not

SELECT
SELECT Year, 
    SUM(CASE WHEN domain_quarter = 'Q1' THEN indicator_results_result ELSE 0 END) AS Q1,
    SUM(CASE  WHEN domain_quarter = 'Q2' THEN indicator_results_result ELSE 0 END) AS Q2,
    SUM(CASE  WHEN domain_quarter = 'Q3' THEN indicator_results_result ELSE 0 END) AS Q3,
    SUM(CASE  WHEN domain_quarter = 'Q4' THEN indicator_results_result ELSE 0 END) AS Q4

FROM
   v_kpi_front_page

GROUP BY Year

as shown in BOL topic pivot table?

Not sure why you write [Q1] as the items in the list of values for the IN(). Syntax of SQL Server uses single quotes around string values. Square brackets are used for column names that are odd, either because the column name contains spaces, or is also a keyword.

My BOL does not show PIVOT as a valid keyword; I use SQL Server 2000.

 

[mrdenny]

PIVOT is a SQL 2005 command. The method that rac2 posted is the pre SQL 2005 method of making a pivot table.

SELECT 
   domain_quarter, 
   Q1 = ISNULL([Q1], 0), 
   Q2 = ISNULL([Q2], 0), 
   Q3 = ISNULL([Q3], 0), 
   Q4 = ISNULL([Q4], 0)
FROM 
(SELECT domain_quarter, indicator_results_result
FROM v_kpi_front_page) p
PIVOT ( 
   SUM(indicator_results_result)
   FOR domain_quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS Indicator

Give that a try. Note the inner query which I added. This may need some tweaking; as I don't have you table schema I can't test the code.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Chance1234]

I keep getting domain_quarter is a invalid column when i know it exists

Chance,

Filmmaker, gentleman and polla stilo eleous

 

[mrdenny]

Can you post the create table script for this table please, along with some same data.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com