How to do this - 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)

How to do this

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-05-23 : 04:48:22

I had this data...


select tbl.*
from (
	select 1 as orderid, 1 as suppliercode, '11aa' as details union all
	select 1 as orderid, 1 as suppliercode, '11bb' as details union all
	select 1 as orderid, 1 as suppliercode, '11cc' as details union all
	select 1 as orderid, 1 as suppliercode, '11dd' as details union all
	select 1 as orderid, 2 as suppliercode, '12dd' as details union all
	select 1 as orderid, 2 as suppliercode, '12dd' as details union all
	select 2 as orderid, 1 as suppliercode, '21cc' as details union all
	select 2 as orderid, 1 as suppliercode, '21dd' as details union all
	select 2 as orderid, 2 as suppliercode, '22dd' as details union all
	select 2 as orderid, 2 as suppliercode, '22dd' as details 
	) as tbl

what i want is this result...


select tbl.*
from (
	select 1 as orderid, 1 as suppliercode, '11aa' as details, '10' as slipNo union all
	select 1 as orderid, 1 as suppliercode, '11bb' as details, '10' as slipNo union all
	select 1 as orderid, 1 as suppliercode, '11cc' as details, '10' as slipNo union all
	select 1 as orderid, 1 as suppliercode, '11dd' as details, '10' as slipNo union all
	select 1 as orderid, 2 as suppliercode, '12dd' as details, '11' as slipNo union all
	select 1 as orderid, 2 as suppliercode, '12dd' as details, '11' as slipNo union all
	select 2 as orderid, 1 as suppliercode, '21cc' as details, '20' as slipNo union all
	select 2 as orderid, 1 as suppliercode, '21dd' as details, '20' as slipNo union all
	select 2 as orderid, 2 as suppliercode, '22dd' as details, '21' as slipNo union all
	select 2 as orderid, 2 as suppliercode, '22dd' as details, '21' as slipNo 
	) as tbl

Notice the SlipNo column. A new SlipNo is created every new orderid and suppliercode. Format of SlipNo is orderid + autoincremental start with zero.

Any idea? Any help is highly appreciated. thank you in advance.

Want Philippines to become 1st World COuntry? Go for World War 3...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-23 : 04:52:45

see if this helps:
http://www.sqlteam.com/item.asp?ItemID=26939

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-23 : 08:21:36

Hi,

Try somr thing like

select tbl.* into #te
from (
select 1 as orderid, 1 as suppliercode, '11aa' as details union all
select 1 as orderid, 1 as suppliercode, '11bb' as details union all
select 1 as orderid, 1 as suppliercode, '11cc' as details union all
select 1 as orderid, 1 as suppliercode, '11dd' as details union all
select 1 as orderid, 2 as suppliercode, '12dd' as details union all
select 1 as orderid, 2 as suppliercode, '12dd' as details union all
select 2 as orderid, 1 as suppliercode, '21cc' as details union all
select 2 as orderid, 1 as suppliercode, '21dd' as details union all
select 2 as orderid, 2 as suppliercode, '22dd' as details union all
select 2 as orderid, 2 as suppliercode, '22dd' as details
) as tbl

Select t.*, cast(orderid as varchar(20)) +
cast( (Select count(distinct suppliercode) from #te where suppliercode < t.suppliercode and orderid = t.orderid) as varchar(10))
from #te t

drop table #te

if u r table is large then go for update instead of the inline select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources