Showing posts with label pivot. Show all posts
Showing posts with label pivot. Show all posts

Monday, March 26, 2012

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
>
Posted by mod at 1:04 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

dynamic pivot table

hi

i am trying to create a dynamic sql statement so i can pivot information...

my problem is that when i run this query i get the error information below. i am running sql server 2000... can this run in 2005?

this runs perfectly when only one record is returned in the sub query...

declare @.query varchar(300)
select @.query = 'Select '+ char(10) + (
select Code
+'case when Code = '''+ Code +''' then count(Code) else 0 end as '+Code+''+char(10)
as [text()]

from WipAvailable WA
)+ char(10) +
' from WipMaster group by Code'

select @.query
exec (@.query)

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

u have given the answer..."this runs perfectly when only one record is returned in the sub query...", in this case the outer query just expects one result to be returned by the subquery... and it'll give the same error in 2005 as well... u need to look at the logic...

i dont know ur exact aim..if pivot is the aim , there is a pivot operator u can use in sql server 2005, to make row data into columns...

|||

if you are using the sql server 2005 then use Pivot operator,

example,

SELECT
[1] as [Code-1],
[2] as [Code-2],
[3] as [Code-3]
FROM
(SELECT Code from WipAvailable) Master
PIVOT
(Count(Code) for Code in ([1],[2],[3]))
AS pvt

to achive the above query dynamically use the following statement,

Declare @.Columns as varchar(1000);
Declare @.Values as varchar(1000);
Declare @.Query as varchar(3000);
select
@.Columns = Isnull(@.Columns,'') + '[' + Code + '] as [Code-' + Code + '],' ,
@.Values = Isnull(@.Values,'') + '[' + Code + '],'
From WipMaster

select @.Columns = Substring(@.Columns,1,Len(@.Columns)-1),
@.Values = Substring(@.Values,1,Len(@.Values)-1)

select @.Query = 'Select ' + @.Columns + ' From (Select Code From WipAvailable) Master'
+ ' Pivot (Count(Code) for Code in (' + @.values + ')) as Pvt'

Exec (@.Query)

|||

To work on SQL Server 2000,

Use the following query

Select
(Select count(Code) from WipAvailable where Code=1) as [1]
,(Select count(Code) from WipAvailable where Code=2) as [2]
,(Select count(Code) from WipAvailable where Code=3) as [3]

To achive the above query dynamically use the following statement


Declare @.query varchar(8000)
select @.query = Isnull(@.query,'') + '(Select count(Code) '
+ char(10) +
' from WipAvailable where Code=' + Code + ') as [' + Code + '],'
from Wipmaster Master
Order By Code
select @.query = 'Select ' + Substring(@.query,1,Len(@.query)-1)
Exec (@.query)

Posted by mod at 1:03 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;


SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql
Posted by mod at 1:03 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Dynamic pivot of resultset

Hi,
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,

> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>
Posted by mod at 1:02 AM 0 comments
Labels: , , , , , , , , , , , , ,

Dynamic Pivot

Hi

Is there a way for using the PIVOT synatx with a dyamic/unknown list of columns? In most PIVOT examples ther columns are "hard" coded but if for example, you are pivoting a sales order table where u don't know which Fiscal Years the orders cover, how could you do this?

Thanks

Hi,

I followed a link http://www.sqlmag.com/Article/ArticleID/43140/sql_server_43140.html

and get this one working in another forum.

http://forums.asp.net/2/1392175/ShowThread.aspx#1392175

If you have question for that, please let me know.

|||Thanks limno, this helps a lot
Posted by mod at 1:01 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Wednesday, March 7, 2012

Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant
Posted by mod at 5:16 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)