March 6, 2014 at 4:39 am
Purpose : I would like to get the minimum value for columns group per row.
Example :
ID , ProductName , Cost1 , Cost2 , Cost3 , Cost4
1 , Cell Phone , 10 , 50 , 25 , 5
2 , TV , 60 , 30 , 45 , 70
3 , Camera , 10 , 30 , 80 , 20
4 , LapTop , 60 , 80 , 10 , 40
I would like to get the result like that :
1 , Cell Phone , 5
2 , TV , 30
3 , Camera , 10
4 , LapTop , 10
Explain : I want to get the minimum Cost for each product among all costs columns "Cost1, Cost2, Cost3, Cost4"
So any Suggestion about how to handle that ??
March 6, 2014 at 4:54 am
It would be much easier to write a query to answer your problem (and test it) if you would provide table DDL and sample data.
See the first link in my signature on how to do this.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 5:02 am
As you probably are allready aware of, this is a bad table design ;-). Read up about normalisation (http://www.sqlservercentral.com/blogs/jamesserra/2012/01/06/normalizing-your-database/) for better design.
Now back to your situation: A possible solution is to PIVOT the table or use Cross Tabs. This will turn the columns [cost1], [cost2], etc. to rows where you can use the MIN function to get the lowest value. Read through this article of Jeff Moden about PIVOT and Cross tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/
March 6, 2014 at 5:43 am
SSCrazy ..Thanks for help but applying normalization rules will be hard little bit in my situations
March 6, 2014 at 6:01 am
ahmedhussein874 (3/6/2014)
SSCrazy ..Thanks for help but applying normalization rules will be hard little bit in my situations
That's why I posted that remark with a smiley!
Have you read the rest of my post?
Now back to your situation: A possible solution is to PIVOT the table or use Cross Tabs. This will turn the columns [cost1], [cost2], etc. to rows where you can use the MIN function to get the lowest value. Read through this article of Jeff Moden about PIVOT and Cross tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/
If you want a better answer including a code-sample you have to give us some DDL and sample data as Koen Verbeeck allready asked you to do.
March 6, 2014 at 7:04 am
Here's my solution.
DECLARE @test-2 TABLE ( ID INT IDENTITY
, ProductName VARCHAR(30)
, Cost1 MONEY
, Cost2 MONEY
, Cost3 MONEY
, Cost4 MONEY )
INSERT INTO @test-2
( ProductName, Cost1, Cost2, Cost3, Cost4 )
VALUES ( 'Cell Phone', 10, 50, 25, 5 ),
( 'TV', 60, 30, 45, 70 ),
( 'Camera', 10, 30, 80, 20 ),
( 'LapTop', 60, 80, 10, 40 )
--SELECT * FROM @test-2
SELECT ID, ProductName, MIN(Cost) as MinCost
FROM @test-2
UNPIVOT
( Cost FOR Product IN (Cost1, Cost2, Cost3, Cost4)
) AS unpvt
GROUP BY ID, ProductName
March 6, 2014 at 1:23 pm
And one using CASE
select Id, ProductName,cMin from
(
select ID, ProductName, case when cost1<cost2 then cost1 else cost2 end c1,case when cost3<cost3 then cost3 else cost4 end c2
from table_name outer apply (select case when c1<c2 then c1 else c2 end cmin) b) a
March 19, 2014 at 10:48 am
Thanks a lot for all your contributions
MysteryJimbo .. Best Solution 🙂
March 19, 2014 at 11:17 am
Just wanted to add a different approach to unpivot explained in this article:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
--Using MysteryJimbo data
SELECT ID, ProductName, MIN(Cost) as MinCost
FROM @test-2
CROSS APPLY (VALUES(Cost1), (Cost2), (Cost3), (Cost4))Costs(Cost)
GROUP BY ID, ProductName
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply