Transforming columns to rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transforming columns to rows

Author

Topic

Annette
Starting Member

1 Post

Posted - 2013-05-02 : 21:37:55

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

549 Posts

Posted - 2013-05-02 : 21:58:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources