dynamic pivot?

I need to write a report that displays the time each operation was started
across the page like this:
Ord_Num OC020 OC030
-- --- --
---
520208 2004-10-01 08:22:00.000
2004-10-01 08:25:00.000
520273 2004-10-01 08:13:00.000
2004-10-01 08:16:00.000
520292 2004-10-01 08:04:00.000
2004-10-01 08:06:00.000
520324 2004-10-01 08:30:00.000
2004-10-01 08:33:00.000
520388 2004-10-01 08:27:00.000
2004-10-01 08:31:00.000
520472 2004-10-01 08:26:00.000
2004-10-01 08:30:00.000
The problem is that I don't know at design time what the operations are all
going to be. Here is some SQL to set up the problem:
Use Pubs
go
Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
PlannedStartDT datetime)
go
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520324', 'PR120', 'Oct 1 2004 8:52AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:28AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:32AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:27AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:31AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC020', 'Oct 1 2004 8:26AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR010', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR020', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR120', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR130', 'Oct 1 2004 8:53AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC020', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR130', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC020', 'Oct 1 2004 8:09AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR010', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR020', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR120', 'Oct 1 2004 8:41AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC020', 'Oct 1 2004 8:12AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC030', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR010', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR020', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR120', 'Oct 1 2004 8:49AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC020', 'Oct 1 2004 8:01AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC030', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR120', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC020', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC030', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:06AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:55AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC020', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC020', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC030', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR130', 'Oct 1 2004 8:39AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR140', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR150', 'Oct 1 2004 8:51AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC020', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC030', 'Oct 1 2004 8:04AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR120', 'Oct 1 2004 8:16AM' )
go
I get nice results with the query below, but again, at design time I don't
know how many operations there will be or what they are.
Select Ord_Num,
OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
From ProcessControl
Group By Ord_Num
Is there any solution to this that does not require dynamic SQL?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyHi
You may want to look at:
http://www.windowsitpro.com/SQLServ...5608/15608.html
but it also uses dynamic SQL.
John
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||You might want to check out www.rac4sql.net for a solution to your problem.
Also, check out
http://www.windowsitpro.com/Windows...54/pg/2/2.html.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>