Creating Cross Tab Query (Pivot Table) in SQL Server 2000

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 2000 Forums

SQL Server Development (2000)

Creating Cross Tab Query (Pivot Table) in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-22 : 10:03:32

Ray writes "I'm using SQL Server 2000
------------------------------------------------------------------The Tables:
----------------------------------------------------------------CREATE TABLE [dbo].[cm_quote] (
[quote_Id] [int] NULL ,
[customer_name] [nvarchar] (30)
) ON [PRIMARY]
GO
----------------------------------------------------------------CREATE TABLE [dbo].[rate_matrix] (
[quote_id] [int] NULL ,
[product_cd] [nvarchar] (10) ,
[mrt_Id] [int] NULL ,
[calc_rate] [decimal] (9, 2) NULL
) ON [PRIMARY]
GO
----------------------------------------------------------------CREATE TABLE [dbo].[cm_product] (
[code] [char] (2) ,
[description] [nvarchar] (20) ,
) ON [PRIMARY]
GO
----------------------------------------------------------------CREATE TABLE [dbo].[quote_mrt] (
[quote_id] [int] NULL ,
[mrt_id] [int] NULL
) ON [PRIMARY]
GO
------------------------------------------------------------------The Data:
----------------------------------------------------------------insert into cm_quote values (1,'John Smith')
GO
----------------------------------------------------------------insert into quote_mrt values (1,3)
insert into quote_mrt values (1,7)
insert into quote_mrt values (1,9)
GO
----------------------------------------------------------------insert into cm_product values ('AA','SuperMed')
insert into cm_product values ('BB','Traditional')
insert into cm_product values ('CC','HMO-II')
GO
----------------------------------------------------------------insert into rate_matrix values (1,'AA',3,128.5)
insert into rate_matrix values (1,'AA',7,227.5)
insert into rate_matrix values (1,'AA',9,310.5)
insert into rate_matrix values (1,'BB',3,135.5)
insert into rate_matrix values (1,'BB',7,239.5)
insert into rate_matrix values (1,'BB',9,324.5)
insert into rate_matrix values (1,'CC',3,167.5)
insert into rate_matrix values (1,'CC',7,251.5)
insert into rate_matrix values (1,'CC',9,338.5)
GO
------------------------------------------------------------------Microsofts Query (or their solution)
----------------------------------------------------------------SELECT [product_cd],
SUM(CASE [mrt_id] WHEN 3 THEN [calc_rate] ELSE 0 END) AS [mrt_3],
SUM(CASE [mrt_id] WHEN 7 THEN [calc_rate] ELSE 0 END) AS [mrt_7],
SUM(CASE [mrt_id] WHEN 9 THEN [calc_rate] ELSE 0 END) AS [mrt_9]
FROM [dbo].[rate_matrix] [rm]
WHERE [quote_id] = 1
GROUP BY [product_cd]
------------------------------------------------------------------Microsofts Results (which are correct)
----------------------------------------------------------------product_cd mrt_3 mrt_7 mrt_9
---------- --------- --------- ---------
AA 128.50 227.50 310.50
BB 135.50 239.50 324.50
CC 167.50 251.50 338.50
------------------------------------------------------------------My Query
----------------------------------------------------------------SELECT [rm].[product_cd],[cp].[description],[rm].[mrt_id], [rm].[calc_rate]
FROM [dbo].[rate_matrix] [rm]
INNER JOIN [dbo].[cm_product] [cp] ON [cp].[code] = [rm].[product_cd]
WHERE [rm].[quote_id] = 1
GROUP BY [rm].[product_cd], [cp].[description], [rm].[calc_rate], [rm].[mrt_id]
------------------------------------------------------------------My Results (without the mrt_id case statements since they are not consistent) which need to be pivoted
----------------------------------------------------------------product_cd description mrt_id calc_rate
---------- -------------------- ----------- -----------
AA SuperMed 3 128.50
AA SuperMed 7 227.50
AA SuperMed 9 310.50
BB Traditional 2 135.50
BB Traditional 7 239.50

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources