Pivot / Unpivot Help - 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

General SQL Server Forums

New to SQL Server Programming

Pivot / Unpivot Help

Author

Topic

Chamark
Starting Member

28 Posts

Posted - 2011-01-24 : 15:53:33

Using MS-SQL2008 - Beginner. Have three tables that I link together. I need to turn columns into rows. I think I need to use Pivot/Unpivot but will accept all help and suggestions. Results should look like this
KPI_Name| DataSource_Name| Bucket 01/01/2010 02/01/2010 03/01/2010 04/01/2010
Rate ABA Rate ABA (DB) Actual_Month 0.0234 xxx xxx xxx
Rate ABA Rate ABA (DB) Actual_YTD 1.1234 xxx xxx xxx
Rate ABA Rate ABA (DB) Forecast_Month 0 xxx xxx xxx
Rate ABA Rate ABA (DB) Forecast_YTD 0 xxx xxx xxx
Rate ABA Rate ABA (DB) Target_Month 0.04 xxx xxx xxx
Rate ABA Rate ABA (DB) Target_YTD 0.04 xxx xxx xxx

So I am think something like this

;with cte as (select DataSource_ID, YearMonth, Bucket, [Value] as [01/01/2010]
from t_kpi_Data T
UNPIVOT ([Value] For Bucket IN ([Actual_Month],
[Forecast_Month], [Target_Month], [Actual_YTD], [Forecast_YTD] , [Target_YTD])) unpvt
where YearMonth = '20100101' )

select D.DataSource_Name, K.KPI_Name, C.YearMonth, Bucket, [01/01/2010]
from cte C inner join dbo.t_kpi_DataSources D on C.DataSource_ID = D.ID
INNER JOIN dbo.t_KPIs K on D.KPI_ID = K.ID

WHERE DataSource_ID = '1295'

But I want the date colums to generate themselves based on passing start and end date parameters - say between '01/01/2010' AND '04/01/2010'

Any help is greatly appreciated

These are the tables

CREATE TABLE [dbo].[t_kpi_Data](
[DataSource_ID] [int] NOT NULL,
[YearMonth] [datetime] NOT NULL,
[State_ID] [int] NULL,
[Actual_Month] [float] NULL,
[Forecast_Month] [float] NULL,
[Target_Month] [float] NULL,
[Actual_YTD] [float] NULL,
[Forecast_YTD] [float] NULL,
[Target_YTD] [float] NULL,

CONSTRAINT [PK_t_kpi_Data] PRIMARY KEY CLUSTERED
(
[DataSource_ID] ASC,
[YearMonth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[t_kpi_DataSources](
[ID] [int] NOT NULL,
[KPI_ID] [int] NOT NULL,
[DataSource_Name] [varchar](60) NOT NULL,
[SecurityLevel] [int] NOT NULL,
[DataSource_Active] [int] NOT NULL,
[ServiceCategory_ID] [int] NULL,

CONSTRAINT [PK_kpi_DataSources] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[t_KPIs](
[ID] [int] NOT NULL,
[KPI_Name] [varchar](60) NOT NULL,

CONSTRAINT [PK_KPIs] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Chamark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-11 : 09:47:37

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-12 : 03:24:03

if your front end is reporting tool, you can generate dynamic there also using containers like matrix in SQL reporting services

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources