Need help creating Query - 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)

Need help creating Query

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-29 : 09:11:15

Hi friends,

I could use some help creating a query. The source table is like this:

Create Table Batch (BatchID as integer, Strategy as varchar (100), Description as varchar(100), Value as varchar (100))
Insert into Batch Values(1000001, 'Startmixing', NULL, NULL)
Insert into Batch Values(1000001, NULL, 'pump', '25%')
Insert into Batch Values(1000001, NULL, 'mixer', '20%')
Insert into Batch Values(1000002, NULL, 'pump', '50%')
Insert into Batch Values(1000002, NULL, 'valve1', '30%')
Insert into Batch Values(1000002, NULL, 'speed', '10m/s')
Insert into Batch Values(1000002, NULL, 'amount', '1000kg')

And I want this out:

BatchID, Operation, Variables
(100001, Startmixing, NULL)
100001, Startmixing, pump 25%
100001, Startmixing, mixer 20%
100002, No_Operation, pump 50%
100002, No_Operation, valve1 30%
100002, No_Operation, speed 10m/s
100002, No_Operation, amount 1000kg

It could also be simplified to this:

startabel
1 operation1
1 Null
2 Null
2 Null

query
1 operation1
1 operation1
2 Null
2 Null

I can't solve it yet, anyone suggestions please?

Grtz Djorre

degraft
Starting Member

10 Posts

Posted - 2009-05-29 : 09:38:32

Hi djorre,

Your query should look like this:

Select BatchID,COALESCE(Strategy,'No_Operation'),Description,Value
From Batch

Think this should do.

Regards
Degraft

DD

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-29 : 09:46:49

Hi try this

Create Table #Batch (BatchID integer, Strategy varchar (100), [Description] varchar(100), [Value] varchar (100))
go
Insert into #Batch Values(1000001, 'Startmixing', NULL, NULL)
Insert into #Batch Values(1000001, NULL, 'pump', '25%')
Insert into #Batch Values(1000001, NULL, 'mixer', '20%')
Insert into #Batch Values(1000002, NULL, 'pump', '50%')
Insert into #Batch Values(1000002, NULL, 'valve1', '30%')
Insert into #Batch Values(1000002, NULL, 'speed', '10m/s')
Insert into #Batch Values(1000002, NULL, 'amount', '1000kg')

--select * from #batch

select b.batchid,
isnull(bb.Operation,'No_Operation'), [Description]+' '+[Value] as Variables
from #batch b
inner join
( select batchid,max(strategy) as Operation
from #batch
group by batchid) as bb on bb.batchid = b.batchid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 03:00:51

can there be a case like this?
Insert into Batch Values(1000001, 'Startmixing', NULL, NULL)
Insert into Batch Values(1000001, NULL, 'pump', '25%')
Insert into Batch Values(1000001, 'Some other string', 'mixer', '20%')

if yes, in such case what should be your output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources