SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transforming columns to rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Annette
Starting Member

Australia
1 Posts

Posted - 05/02/2013 :  21:37:55  Show Profile  Reply with Quote
I have joined two tables with the following code, but I need all column headings, except the year code, to become rows with the values under each year. It needs to be like this for use in an SSRS report. I have tried unpivot which does not work (at least not for this novice). I also have some old code which uses a CASE approach, but I am not sure that is the right way to go about it as the report is quite big and I'm worried about the rendering time. Any help would be appreciated. Thanks.

declare @Year as varchar(4)
Set @Year='2011'
declare @Cluster as varchar(4)
set @Cluster ='908'
declare @Agency as varchar(4)
set @Agency='294'


SELECT FTE_DY, FTE_DY_Non_Cas,FTE_Census, FTE_Census_Non_Cas,A.Year_Code
FROM
((
SELECT Year_Code, SUM(FTE)FTE_DY_Non_Cas,SUM(FTE_Census_Period)FTE_Census_Non_Cas
FROM DM.FACT_EMPLOYMENT_POSITION FACT_POS
INNER JOIN DM.DIM_PERIOD DIM_PER
ON FACT_POS.Dim_Period_SK = DIM_PER.Dim_Period_SK
INNER JOIN DM.DIM_EMPLOYMENT DIM_EMPLMT
ON FACT_POS.Dim_Employment_SK = DIM_EMPLMT.Dim_Employment_SK
INNER JOIN DM.DIM_EMPLOYEE DIM_EMP ON
FACT_POS.Dim_Employee_SK = DIM_EMP.Dim_Employee_SK
INNER JOIN DM.DIM_WFP_REPORTING_ENTITY DIM_ENT ON
DIM_ENT.Reporting_Entity_Code = DIM_EMP.Reporting_Entity_Code
WHERE Period_Level_Code like 'YEAR'
AND(Year_Code like @Year or Year_Code like @Year-1 or Year_Code like @Year-2)
AND Cluster_Code =@Cluster
AND DIM_ENT.Reporting_Entity_Code = @Agency
and DIM_ENT.Is_Latest ='Y'
AND Casual_Non_Casual_Group_Code ='NC'
GROUP BY Year_Code,Casual_Non_Casual_Group_Code, Casual_Non_Casual_Group_Desc)A

INNER JOIN

(SELECT Year_Code,SUM(FTE)FTE_DY,SUM(FTE_Census_Period)FTE_Census
FROM DM.FACT_EMPLOYMENT_POSITION FACT_POS
INNER JOIN DM.DIM_PERIOD DIM_PER
ON FACT_POS.Dim_Period_SK = DIM_PER.Dim_Period_SK
INNER JOIN DM.DIM_EMPLOYEE DIM_EMP ON
FACT_POS.Dim_Employee_SK = DIM_EMP.Dim_Employee_SK
INNER JOIN DM.DIM_WFP_REPORTING_ENTITY DIM_ENT ON
DIM_ENT.Reporting_Entity_Code = DIM_EMP.Reporting_Entity_Code
WHERE Period_Level_Code like 'YEAR'
AND(Year_Code like @Year or Year_Code like @Year-1 or Year_Code like @Year-2)
AND Cluster_Code =@Cluster
AND DIM_ENT.Reporting_Entity_Code = @Agency
and DIM_ENT.Is_Latest ='Y'
GROUP BY Year_Code) B

ON A.Year_Code =B.Year_Code )

ORDER BY Year_Code

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/02/2013 :  21:58:26  Show Profile  Reply with Quote
It is difficult to figure out what you want exactly.
You may want to take a look at the following article which would help you to post the question (for example: what is the expected output, what is your current output, your DDLs, data etc...) in a way others can understand and answer faster.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000