Conditional Transpose - 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 2005 Forums

Transact-SQL (2005)

Conditional Transpose

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-06-16 : 08:58:36

I have the below table
List_Jobs
Job GroupID SequenceNo DestID WaitTime
1 1 1 31 15:13:57.950
1 1 2 11 15:13:57.950
1 1 3 21 15:13:57.950
1 1 4 41 15:13:57.950
1 2 5 141 16:23:18.967
1 2 6 12 16:23:18.967
1 2 7 23 16:23:18.967
1 2 8 42 16:23:18.967
1 3 9 142 18:49:03.090
1 3 10 61 18:49:03.090
1 3 11 22 18:49:03.090
1 3 12 42 18:49:03.090
1 4 13 142 19:02:52.950
1 4 14 13 19:02:52.950
1 4 15 43 19:02:52.950
2 1 1 31 21:13:57.950
2 1 2 61 21:13:57.950
2 1 3 22 21:13:57.950
2 1 4 41 21:13:57.950
2 2 5 141 21:23:18.967
2 2 6 13 21:23:18.967
2 2 7 23 21:23:18.967
2 2 8 42 21:23:18.967
2 3 9 142 21:49:03.090
2 3 10 61 21:49:03.090
2 3 11 22 21:49:03.090
2 3 12 42 21:49:03.090

Result Required:
JobID GroupID WaitTime Path1 Path2 Path3 Path4
1 1 15:13:57.950 31 11 21 41
1 2 16:23:18.967 141 12 23 42
1 3 18:49:03.090 142 61 22 42
1 4 19:02:52.950 142 13 43 NULL
2 1 21:13:57.950 31 61 22 41
2 2 21:23:18.967 141 13 23 42
2 3 21:49:03.090 142 61 22 42

I have made the below query:

SELECT [JobID] ,[GroupID] ,[WaitTime] ,
max(case [SequenceNo] when 1 then [DestID] else NULL end) as Path1,
max(case [SequenceNo] when 2 then [DestID] else NULL end) as Path2,
max(case [SequenceNo] when 3 then [DestID] else NULL end) as Path3,
max(case [SequenceNo] when 4 then [DestID] else NULL end) as Path4
FROM [iCA].[dbo].[List_Jobs]
group by [JobID], [GroupID] ,[WaitTime]

I need to make when 1,2,3,4 change to Minimum of SequenceNo of the Group

Any suggestions , You can please suggest any other method

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 11:30:24

[code]
declare @List_Jobs table
(
Job int,
GroupID int,
SequenceNo int,
DestID int,
WaitTime datetime
)
insert into @List_Jobs
select 1, 1, 1, 31, '15:13:57.950' union all
select 1, 1, 2, 11, '15:13:57.950' union all
select 1, 1, 3, 21, '15:13:57.950' union all
select 1, 1, 4, 41, '15:13:57.950' union all
select 1, 2, 5, 141, '16:23:18.967' union all
select 1, 2, 6, 12, '16:23:18.967' union all
select 1, 2, 7, 23, '16:23:18.967' union all
select 1, 2, 8, 42, '16:23:18.967' union all
select 1, 3, 9, 142, '18:49:03.090' union all
select 1, 3, 10, 61, '18:49:03.090' union all
select 1, 3, 11, 22, '18:49:03.090' union all
select 1, 3, 12, 42, '18:49:03.090' union all
select 1, 4, 13, 142, '19:02:52.950' union all
select 1, 4, 14, 13, '19:02:52.950' union all
select 1, 4, 15, 43, '19:02:52.950' union all
select 2, 1, 1, 31, '21:13:57.950' union all
select 2, 1, 2, 61, '21:13:57.950' union all
select 2, 1, 3, 22, '21:13:57.950' union all
select 2, 1, 4, 41, '21:13:57.950' union all
select 2, 2, 5, 141, '21:23:18.967' union all
select 2, 2, 6, 13, '21:23:18.967' union all
select 2, 2, 7, 23, '21:23:18.967' union all
select 2, 2, 8, 42, '21:23:18.967' union all
select 2, 3, 9, 142, '21:49:03.090' union all
select 2, 3, 10, 61, '21:49:03.090' union all
select 2, 3, 11, 22, '21:49:03.090' union all
select 2, 3, 12, 42, '21:49:03.090'

-- Query
;with data (Job, GroupID, WaitTime, DestID, row_no)
as
(
select Job, GroupID, WaitTime, DestID, row_no = row_number() over (partition by Job, GroupID order by SequenceNo)
from @List_Jobs
)
select Job, GroupID, WaitTime, Path1 = [1], Path2 = [2], Path3 = [3], Path4 = [4]
from data
pivot
(
max(DestID)
for row_no in ([1], [2], [3], [4])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources