3 Min Values (7 Column Outcome Required)

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

Old Forums

CLOSED - General SQL Server

3 Min Values (7 Column Outcome Required)

Author

Topic

Weekend
Starting Member

20 Posts

Posted - 2006-04-24 : 02:00:48

Hello
This is my 1st ever post on this forum.
Warm wishes for Members & Moderators. Wish to exchange strong long term love, support & learning from all of you.

Tab1
Col_Item varchar(20),
Col_Shop varchar(20),
Col_Rate real

Many shops sales several items at different rate.
I wish to have 3 most min rate shop names with rates against every item.

Current Statues
Col_Item || Col_Shop || Col_Rate
Item1 || Shop1 || 1.2
Item1 || Shop2 || 2.0
Item1 || Shop3 || 3.0
Item1 || Shop4 || 3.2
Item2 || Shop1 || 3.0
Item2 || Shop2 || 3.3
Item2 || Shop3 || 2.5
Item2 || Shop4 || 2.9

Required Outcome (In 7 Columns)
Col_Item || MinShop1 || MinRate1 || MinShop2 || MinRate2 || MinShop3 || MinRate3
Item1 || Shop1 || 1.2 || Shop2 || 2.0 || Shop3 || 3.0
Item2 || Shop3 || 2.5 || Shop4 || 2.9 || Shop1 || 3.0

Please advise how to get the required outcome.

Wishes
Jawad

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 02:10:03

Look for Cross-tab reports in sql server help file

Also refer
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-24 : 02:11:01

I guess you need to use Pivot tables, Check out the link if its helps..

http://www.sqlteam.com/item.asp?ItemID=2955

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-24 : 02:11:14

[code]declare @table table
(
Col_Item varchar(20),
Col_Shop varchar(20),
Col_Rate real
)

insert into @table
select 'Item1', 'Shop1', 1.2 union all
select 'Item1', 'Shop2', 2.0 union all
select 'Item1', 'Shop3', 3.0 union all
select 'Item1', 'Shop4', 3.2 union all
select 'Item2', 'Shop1', 3.0 union all
select 'Item2', 'Shop2', 3.3 union all
select 'Item2', 'Shop3', 2.5 union all
select 'Item2', 'Shop4', 2.9

select Col_Item,
max(case when rowid = 1 then Col_Shop else NULL end) as MinShop1,
max(case when rowid = 1 then Col_Rate else NULL end) as MinRate1,
max(case when rowid = 2 then Col_Shop else NULL end) as MinRate2,
max(case when rowid = 2 then Col_Rate else NULL end) as MinRate2,
max(case when rowid = 3 then Col_Shop else NULL end) as MinRate3,
max(case when rowid = 3 then Col_Rate else NULL end) as MinRate3
from
(
select Col_Item, Col_Shop, Col_Rate,
rowid = (select count(*) from @table x where x.Col_Item = t.Col_Item and x.Col_Rate <= t.Col_Rate)
from @table t
where Col_Rate in (select top 3 Col_Rate from @table x where x.Col_Item = t.Col_Item order by Col_Rate)
)a
group by Col_Item[/code]

Weekend
Starting Member

20 Posts

Posted - 2006-04-24 : 02:38:53

thanks madhivanan & chiragkhabaria
Special Thanks for khtan you make it look so simple

Wishes
Jawad

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-24 : 06:42:19

Hi all,

I noticed that the current solution didn't seem to cope with ties (I don't know whether this was a requirement or not), so I've had a go at improving it.

It seems performs a bit quicker too...

--data
declare @table table
(
	Col_Item	varchar(20),
	Col_Shop	varchar(20),
	Col_Rate	real
)

insert into @table
select	'Item1', 'Shop1', 1.2	union all
select	'Item1', 'Shop2', 2.0	union all
select	'Item1', 'Shop3', 3.0	union all
select	'Item1', 'Shop4', 3.2
                            	union all
select	'Item2', 'Shop1', 3.0	union all
select	'Item2', 'Shop2', 3.3	union all
select	'Item2', 'Shop3', 2.5	union all
select	'Item2', 'Shop4', 2.9
                                union all
select	'Item3', 'Shop1', 1.0	union all
select	'Item3', 'Shop2', 2.0	union all
select	'Item3', 'Shop3', 3.0	union all
select	'Item3', 'Shop4', 3.0   union all
select	'Item3', 'Shop5', 5.0	
                                union all
select	'Item4', 'Shop1', 2.0	union all
select	'Item4', 'Shop2', 1.0	union all
select	'Item4', 'Shop3', 1.0	union all
select	'Item4', 'Shop4', 1.0   union all
select	'Item4', 'Shop5', 1.0	

--calculation
select Col_Item, 
	max(case when rowid = 1 then Col_Shop else NULL end) as MinShop1,
	max(case when rowid = 1 then Col_Rate else NULL end) as MinRate1,
	max(case when rowid = 2 then Col_Shop else NULL end) as MinRate2,
	max(case when rowid = 2 then Col_Rate else NULL end) as MinRate2,
	max(case when rowid = 3 then Col_Shop else NULL end) as MinRate3,
	max(case when rowid = 3 then Col_Rate else NULL end) as MinRate3
from (
    select a.Col_Item, a.Col_Shop, min(a.Col_Rate) as Col_Rate, count(*) as rowid
    from @table a inner join @table b on a.Col_Item = b.Col_Item and 
        ((a.Col_Rate != b.Col_Rate and a.Col_Rate >= b.Col_Rate) or 
         (a.Col_Rate = b.Col_Rate and a.Col_Shop >= b.Col_Shop))
    group by a.Col_Item, a.Col_Shop
    having count(*) <= 3
     ) a
group by Col_Item