July 17, 2008 at 12:44 pm
Hi,
I have a table that has a history of transactions and is defined as
CREATE TABLE [dbo].[ORDER_STATUS_HISTORY](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Order_ID] [int] NOT NULL,
[Order_Status_Cd] [varchar](50) NOT NULL,
[StatusDate] [datetime] NOT NULL,
CONSTRAINT [PK_DW_ORDER_STATUS_HISTORY] 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]
When I try to pivot the data by status using the follwoing query I get one row for every status of an order
Select Order_ID, [Pending],[Submitted],[Received],[Processing-CheckedIn],[Processing],[Processed],[Archived],[Discard]
from (Select * from dbo.DW_ORDER_STATUS_HISTORY)ps
Pivot
(
Max(StatusDate) for Order_Status_Cd in
([Pending],[Submitted],[Received],[Processing-CheckedIn],[Processing],[Processed],[Archived],[Discard])
)
As PivotTable
order by 1
returns
Order_ID Pending Recieved ......................
-------- ------- -------- -------------------
12345 1/1/2003 Null Null
12345 Null 1/3/2003 Null
12345 Null Null 1/7/2007
where I like it to return
Order_ID Pending Recieved ......................
-------- ------- -------- -------------------
12345 1/1/2003 1/3/2003 1/7/2007
Any ideas about what I am doing wrong?
July 17, 2008 at 12:52 pm
I recently blogged about that.
Have a look at http://weblogs.sqlteam.com/peterl/archive/2008/07/15/PIVOT-conundrum.aspx
Change you derive table ps to contain only the records present in the pivot. all other columns are used for transformation as well, even if you do not see them.
N 56°04'39.16"
E 12°55'05.25"
July 17, 2008 at 12:53 pm
SELECTOrder_ID,
[Pending],
[Submitted],
[Received],
[Processing-CheckedIn],
[Processing],
[Processed],
[Archived],
[Discard]
FROM(
SELECTStatusDate,
Order_Status_Cd
FROMdbo.DW_ORDER_STATUS_HISTORY
) ps
Pivot(
Max(StatusDate)
for Order_Status_Cd in ([Pending],[Submitted],[Received],[Processing-CheckedIn],[Processing],[Processed],[Archived],[Discard])
) As PivotTable
order by 1
N 56°04'39.16"
E 12°55'05.25"
July 17, 2008 at 2:21 pm
It worked great, Thank you for the reply. Do you know how I can change the query to return count by status by day?
Date Pending submitted Received
------ ------- ---------- ---------
1/1/2007 5 6 4
1/2/2007 3 2 1
I am using the following query but I am having a similar problem
Select Convert(varchar(10),StatusDate,112) , [Pending],[Submitted],[Received],[Processing-CheckedIn],[Processing],[Processed],[Archived],[Discard]
from (
SELECT Order_ID, StatusDate,
Order_Status_Cd
FROM dbo.DW_ORDER_STATUS_HISTORY
)ps
Pivot
(
Count(Order_ID) for Order_Status_Cd in
([Pending],[Submitted],[Received],[Processing-CheckedIn],[Processing],[Processed],[Archived],[Discard])
)
As PivotTable
order by 1
July 17, 2008 at 3:22 pm
what is the expected output?
N 56°04'39.16"
E 12°55'05.25"
July 17, 2008 at 3:24 pm
it would be the count of status by day like
Date Pending submitted Received
------ ------- ---------- ---------
1/1/2007 5 6 4
1/2/2007 3 2 1
July 18, 2008 at 2:25 am
Remove Order_ID from the SELECT-list in the derived table.
N 56°04'39.16"
E 12°55'05.25"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply