Data Manipulation - 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 2000 Forums

Transact-SQL (2000)

Data Manipulation

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2006-11-14 : 15:02:59

I have data in the following table:

FactoryID FactoryJob
-------------------------------------
Factory1 No1
Factory1 No2
Factory2 No1
Factory3 No3
Factory3 No4
Factory3 No5

and I would like the data out in the format of:

FactoryID FactoryJob1 FactoryJob2 FactoryJob3
--------------------------------------------------------------
Factory1 No1 No2 NULL
Factory2 No1 NULL NULL
Factory3 No3 No4 No5

Is there an easy way to do this withouth using a cursor?

Cheers,
Kabir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 15:18:52

[code]-- prepare test data
declare @test table (FactoryID varchar(50), FactoryJob varchar(10))

insert @test
select 'Factory1', 'No1' union all
select 'Factory1', 'No2' union all
select 'Factory2', 'No1' union all
select 'Factory3', 'No3' union all
select 'Factory3', 'No4' union all
select 'Factory3', 'No5'

-- do the work
select factoryid,
max(case when seq = 1 then factoryjob end) AS FactoryJob1,
max(case when seq = 2 then factoryjob end) AS FactoryJob2,
max(case when seq = 3 then factoryjob end) AS FactoryJob3
from (
select t.factoryid,
t.factoryjob,
(select count(*) from @test y where y.factoryid = t.factoryid and cast(substring(y.factoryjob, 3, 8000) as int) <= cast(substring(t.factoryjob, 3, 8000) as int)) AS Seq
from @test t
) d
group by factoryid[/code]

Peter Larsson
Helsingborg, Sweden

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2006-11-15 : 08:23:18

Thanks for that.

My only problem is that the FactoryJob value is usually not a number (I just used simple examples previously).

So my dataset would more likely look like this:

FactoryID FactoryJob
-------------------------------------
Factory1 ABC
Factory1 CDQ
Factory2 ABC
Factory3 QWE
Factory3 ERT
Factory3 TYU

Is there an easy way to get this data out in the format of:

FactoryID FactoryJob1 FactoryJob2 FactoryJob3
--------------------------------------------------------------
Factory1 ABC CDQ NULL
Factory2 ABC NULL NULL
Factory3 QWE ERT TYU

Cheers,
Kabir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 09:16:29

How hard can it be?

-- do the work
select		factoryid,
		max(case when seq = 1 then factoryjob end) AS FactoryJob1,
		max(case when seq = 2 then factoryjob end) AS FactoryJob2,
		max(case when seq = 3 then factoryjob end) AS FactoryJob3
from		(
			select	t.factoryid,
				t.factoryjob,
				(select count(*) from @test y where y.factoryid = t.factoryid and y.factoryjob <= t.factoryjob) AS Seq
			from	@test t
		) d
group by	factoryid

Peter Larsson
Helsingborg, Sweden

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2006-11-16 : 05:01:51

Thanks for that.

Is it possible to extend the example further so that it caters for the following scenario:

FactoryID FactoryJob FactorySubJob
-------------------------------------
Factory1 ABC FRT
Factory1 CDQ NULL
Factory2 ABC NULL
Factory3 QWE DDD
Factory3 ERT WWW
Factory3 TYU NULL

and gives the following output

FactoryID FactoryJob1 FactoryJob2 FactoryJob3 FactoryJob4 FactoryJob5
--------------------------------------------------------------
Factory1 ABC CDQ FRT NULL NULL
Factory2 ABC NULL NULL NULL NULL
Factory3 QWE ERT TYU DDD WWW

As you can probably tell I am not very good at SQL (I am a VB programmer - so I always use cursors..). As such your help is much appreciated.

Cheers,
Kabir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:05:40

Put in some effort yourself, god damn-it! How hard can it be?
How do you think you will survive professionally if you don't take the time to study and understand the suggested code we provide?

-- do the work
select		factoryid,
		max(case when seq = 1 then factoryjob end) AS FactoryJob1,
		max(case when seq = 2 then factoryjob end) AS FactoryJob2,
		max(case when seq = 3 then factoryjob end) AS FactoryJob3,
		max(case when seq = 4 then factoryjob end) AS FactoryJob4,
		max(case when seq = 5 then factoryjob end) AS FactoryJob5,
		...
from		(
			select	t.factoryid,
				t.factoryjob,
				(select count(*) from @test y where y.factoryid = t.factoryid and y.factoryjob <= t.factoryjob) AS Seq
			from	@test t
			union all
			select	t.factoryid,
				t.factorysubjob,
				(select count(*) from @test y where y.factoryid = t.factoryid and y.factorysubjob <= t.factorysubjob)
					+ (select count(*) from @test y where y.factoryid = t.factoryid)
			from	@test t
		) d
group by	factoryid

If you truly want this to be done dynamically, learn about PIVOT and CROSS TAB reports in the excellent aid BOOKS ONLINE. And GOOGLE.

BTW, your table design is horrible. You should consider make a change to normalized data.

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-16 : 08:12:54

Patronizing Saint of Lost Yaks

Jay White

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 08:15:15

Also refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05/15/5175.aspx

Madhivanan

Failing to plan is Planning to fail

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2006-11-17 : 05:30:18

Thanks for that.

I am studying, but its taking me time to get my head around SQL....

Cheers,
Kabir

PS Do you recommend any good books - I have been told Celko's books are good.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-17 : 05:57:48

http://sqlteam.com/store.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources