Combining multiple rows based on criteria

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)

Combining multiple rows based on criteria

Author

Topic

DeviantMagick
Starting Member

3 Posts

Posted - 2010-08-31 : 16:05:40

Hello, this is my first post to the forum. I'm hoping someone can help, or point me in the right direction.

I have the following table:
Product Ser1 Ser2 Ser3 Sale
Orange NULL NULL 135 23.00
Orange NULL NULL 142 23.00
Orange NULL 242 NULL 23.00
Orange 542 NULL NULL 23.00
Orange 549 NULL NULL 23.00
Orange 586 NULL NULL 23.00

Based on this code:


select 
  Product = a.name, 
  Ser1 = 
    case b.serialId when 3 
      then b.serialNbr end, 
  Ser2 = 
    case b.serialId when 4 
      then b.serialNbr end, 
  Ser3 = 
    case b.serialId when 2 
      then b.serialNbr end, 
  Sale = a.sale  
from 
  foundation..Product a 
    join foundation..Serial b on a.productId = b.productId
where 
  a.productId=2

I'm trying to get the results as such:
Product Ser1 Ser2 Ser3 Sale
Orange 542 242 142 23.00

The criteria for Ser1 and Ser3 is:
Ser1: min(Ser1)
Ser3: substring(Ser3,2,2)=substring(min(Ser1),2,2)

Is there any way to accomplish this?

::Joseph::
http://www.facebook.com/DeviantMagick

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-31 : 22:10:50

can you put in sample data from product and serial tables and the desired output. I didn't quite understand from your first post.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-31 : 22:15:51

quote:
The criteria for Ser1 and Ser3 is:
Ser1: min(Ser1)
Ser3: substring(Ser3,2,2)=substring(min(Ser1),2,2)

not really sure what do you want above criteria. Provide some sample data from Product & Serial table with the corresponding expected result

this should give you a some start

select 
  Product = a.name, 
  Ser1 = 
    sum(case b.serialId when 3 
      then b.serialNbr end), 
  Ser2 = 
    sum(case b.serialId when 4 
      then b.serialNbr end), 
  Ser3 = 
    sum(case b.serialId when 2 
      then b.serialNbr end), 
  Sale = sum(a.sale)  
from 
  foundation..Product a 
    join foundation..Serial b on a.productId = b.productId
where 
  a.productId=2
group by
  a.name

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

DeviantMagick
Starting Member

3 Posts

Posted - 2010-09-01 : 17:07:51

I was able to come up with a solution but I had to create a temp table.
Here's the code I used without the hypothetical references:

select 
	propertyId,
	ledgerNbr = min(cast(ledgerNbr as int)-4000)
into 
	#validCSI
from 
	foundation..ledger
where 
	entityId = 3 
	and isnull(propertyId,0)!=0
	and cast(ledgerNbr as int)-4000 between 1001 and 1999
group by propertyId

select
	g.propertyId,
	mhcLedger = min(case g.entityId when 3 
					then cast(g.ledgerNbr as int) end), 
	rsiLedger = min(case g.entityId when 4 
					then cast(g.ledgerNbr as int) end), 
	csiLedger = min(case g.entityId when 2 
					then v.LedgerNbr end)  				
from
	foundation..ledger g
		join #validCSI v on g.propertyId = v.propertyId
where g.propertyId > 0
	and g.ledgerNbr > 0
group by g.propertyId

drop table #validCSI

It might be cumbersome coding since I'm still getting acquainted with the advanced techniques in SQL so if anyone want to suggest how to improve the above please feel free to contribute.

::Joseph::
http://www.facebook.com/DeviantMagick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-01 : 22:03:51

you can use a CTE in-place of the temp table


; with validCSI as
(
select 
	propertyId,
	ledgerNbr = min(cast(ledgerNbr as int)-4000)
into 
	#validCSI
from 
	foundation..ledger
where 
	entityId = 3 
	and isnull(propertyId,0)!=0
	and cast(ledgerNbr as int)-4000 between 1001 and 1999
group by propertyId
)
select
	g.propertyId,
	mhcLedger = min(case g.entityId when 3 
					then cast(g.ledgerNbr as int) end), 
	rsiLedger = min(case g.entityId when 4 
					then cast(g.ledgerNbr as int) end), 
	csiLedger = min(case g.entityId when 2 
					then v.LedgerNbr end)  				
from
	foundation..ledger g
		join #validCSI validCSI v on g.propertyId = v.propertyId
where g.propertyId > 0
	and g.ledgerNbr > 0
group by g.propertyId

drop table #validCSI

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

DeviantMagick
Starting Member

3 Posts

Posted - 2010-09-02 : 09:45:11

Thank you

::Joseph::
http://www.facebook.com/DeviantMagick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources