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
 General SQL Server Forums
 New to SQL Server Programming
 Help Needed SQL Self Join

Author  Topic 

vishwakar
Starting Member

4 Posts

Posted - 2013-05-01 : 23:32:47
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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-01 : 23:51:53
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

52326 Posts

Posted - 2013-05-02 : 01:27:12
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 - 2013-05-02 : 09:24:22
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

52326 Posts

Posted - 2013-05-03 : 00:50:57
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
   

- Advertisement -