SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Needed SQL Self Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishwakar
Starting Member

4 Posts

Posted - 05/01/2013 :  23:32:47  Show Profile  Reply with Quote
Hi I am sitting on it from the past one day but could not come up with the right solution, I am a newbie to sql .. Please Help me in understanding this

Table Part

PARTNUM	SUPPLIERNUM	PARTTYPE	COST	APPLIANCENUM
191	2045	        Handles	         9	12497
191	3728	        Motors	         62	23479
191	2457	        Motors    	 45	32487
192	4568	        Accessories	 20	13789
192	1234	        Motors	        140	38979
193	4345	        Motors	         90	23479
193	1784	        Accessories	  8	38979



To get a grip on the problem, they would like to first identify all part numbers that map to different part types . Write a query that lists part numbers that can have different part types. Order the result by partnum.

Desired Out Put

PARTNUM	firstType secondType
191	Motors	  Handles
192	Motors	  Accessories
193	Motors	  Accessories
202	Motors	  Accessories
292	Pumps	  Motors
293	Pumps	  Motors
392	Pumps	  Motors


My Analysis and code


select distinct p1.partnum,p1.parttype FirstType,p2.parttype Secondtype from part p1,part p2 where 
p1.parttype != p2.parttype and 
p1.partnum = p2.partnum 
order by p1.partnum




PARTNUM	FirstType	Secondtype	
191	Handles  	Motors	
191	Motors	        Handles	
192	Accessories	Motors	
192	Motors	        Accessories	
193	Accessories	Motors	
193	Motors	        Accessories	
202	Accessories	Motors	
202	Motors	        Accessories	
292	Motors	        Pumps	
292	Pumps	        Motors	
293	Motors	        Pumps	
293	Pumps	        Motors	
392	Motors	        Pumps	
392	Pumps	        Motors	


Edited by - vishwakar on 05/01/2013 23:35:39

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/01/2013 :  23:51:53  Show Profile  Reply with Quote
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/02/2013 :  01:27:12  Show Profile  Reply with Quote
what if there are more than two accesories? you still want only two of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vishwakar
Starting Member

4 Posts

Posted - 05/02/2013 :  09:24:22  Show Profile  Reply with Quote
In My Scenario

I will be having only two accesories.

quote:
Originally posted by visakh16

what if there are more than two accesories? you still want only two of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/03/2013 :  00:50:57  Show Profile  Reply with Quote
in that case below is enough


SELECT PARTNUM,
MAX(CASE WHEN Rnk = 1 THEN PARTTYPE END) AS FirstType,
MAX(CASE WHEN Rnk = 2 THEN PARTTYPE END) AS SecondType
FROM (SELECT DENSE_RANK() OVER (PARTITION BY PARTNUM ORDER BY PARTTYPE DESC) AS Rnk,*
      FROM PartTable
      )t
GROUP BY PARTNUM


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000