PIVOT queries - 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)

PIVOT queries

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-28 : 10:41:33

I have the following tables:

OrderHeader (OrderID, TotalAmount, DateCreated)
OrderItem (OrderID, ItemID, Amount)

And I want the following results:


Total Processed (OrderDate)		
Date	OrderHeader	OrderItem
20100728	2	6
20100729	1	1
		
Total Processed Today (DateInserted)		
Date	OrderHeader	OrderItem
20100728	1	2
20100729	0	0
20100730	2	4

So the first table is based on OrderDate
The second on DateInserted

Here is the code to create the tables and insert some data:

CREATE DATABASE [Orders]
GO
USE [Orders]
GO
CREATE TABLE OrderHeader (OrderID INT, TotalAmount MONEY, OrderDate DATETIME, DateInserted DATETIME, CONSTRAINT PK_OrderHeader PRIMARY KEY (OrderID))
GO
CREATE TABLE OrderItem (OrderID INT, ItemID INT, Amount MONEY, OrderDate DATETIME, DateInserted DATETIME, CONSTRAINT PK_OrderItem PRIMARY KEY (OrderID, ItemID))
GO
INSERT INTO OrderHeader
SELECT 1,2.45, '20100728', '20100728'
UNION ALL
SELECT 2, 14.95, '20100728', '20100730'
UNION ALL
SELECT 3, 1.20, '20100729', '20100730'
GO
INSERT INTO OrderItem
SELECT 1, 1, 0.45, '20100728', '20100728'
UNION ALL
SELECT 1, 2, 2.00, '20100728', '20100728'
UNION ALL
SELECT 2, 1, 12.95, '20100728', '20100730'
UNION ALL
SELECT 2, 2, 1.00, '20100728', '20100730'
UNION ALL
SELECT 2, 3, 1.00, '20100728', '20100730'
UNION ALL
SELECT 3, 1, 1.20, '20100729', '20100730'
GO

SELECT * FROM OrderHeader
SELECT * FROM OrderItem

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:55:18

[code]SELECT OrderDate,
COUNT(DISTINCT OrderID) AS OrderHeader,
COUNT(*) AS OrderItem
FROM dbo.OrderItem
GROUP BY OrderDate
ORDER BY OrderDate

SELECT DateInserted,
COUNT(DISTINCT OrderID) AS OrderHeader,
COUNT(*) AS OrderItem
FROM dbo.OrderItem
GROUP BY DateInserted
ORDER BY DateInserted[/code]There seem to be a problem with normalization, since OrderDate is present in both tables.

N 56°04'39.26"
E 12°55'05.63"

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-28 : 11:25:55

Thanks Peso, much appreciated

We do have repeating columns in the tables (the natural key) This is to prevent having to perform multiple joins as we have millions of rows (over a billion in Item). A lot of the search criteria is based around the natural key (not included in the sample as I simplified it for easiness sake). So rather than join to header each time, we go direct to the table.

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 13:55:14

Well... I am currently designing and working on a datawarehouse with unioned "OrderHeaders" and "OrderDetails".
The negligable slower difference in time to get the queries using a JOIN is upweighted with smaller database due to much less data and easier to maintain if one or more columns in the natural key changes (and they do a lot due to human typing errors).

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources