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.
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_CasFROM DM.FACT_EMPLOYMENT_POSITION FACT_POSINNER JOIN DM.DIM_PERIOD DIM_PERON 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 ONFACT_POS.Dim_Employee_SK = DIM_EMP.Dim_Employee_SK INNER JOIN DM.DIM_WFP_REPORTING_ENTITY DIM_ENT ONDIM_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 =@ClusterAND DIM_ENT.Reporting_Entity_Code = @Agencyand 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)AINNER JOIN (SELECT Year_Code,SUM(FTE)FTE_DY,SUM(FTE_Census_Period)FTE_CensusFROM DM.FACT_EMPLOYMENT_POSITION FACT_POSINNER JOIN DM.DIM_PERIOD DIM_PERON FACT_POS.Dim_Period_SK = DIM_PER.Dim_Period_SK INNER JOIN DM.DIM_EMPLOYEE DIM_EMP ONFACT_POS.Dim_Employee_SK = DIM_EMP.Dim_Employee_SK INNER JOIN DM.DIM_WFP_REPORTING_ENTITY DIM_ENT ONDIM_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 =@ClusterAND DIM_ENT.Reporting_Entity_Code = @Agencyand DIM_ENT.Is_Latest ='Y'GROUP BY Year_Code) BON 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 |
|
|
|
|
|
|
|