January 31, 2007 at 8:19 pm
guys, i have these 2 tables, one-to-many relationship, they look like:
table A
ShipName FishingDate FishingLocation
------------------------------------
Titanic 1/1/2007 Northpole
table B
ShipName FishingDate FishSpecies CatchNumber
--------------------------------------------
Titanic 1/1/2007 Jack 1
Titanic 1/1/2007 Rose 2
Titanic 2/1/2007 Jack 2
how do i query to get a result like:
ShipName FishingDate FishingLocation Jack Rose
----------------------------------------------
Titanic 1/1/2007 Northpole 1 2
Titanic 2/1/2007 Northpole 2
dont have a clue, can any one give a hint?
January 31, 2007 at 8:29 pm
I'll let you work out the inner join but the pivot looks like this :
Select
col1,
col2,
case when fish = 'Jack' THEN CatchNumber ELSE NULL END,
case when fish = 'Rose' THEN CatchNumber ELSE NULL END
You can also use sum around the case expression... which I figure you might need to aggregate per day.
February 1, 2007 at 12:02 am
Hi
Its better to use inner join to avoid any manual addition in your query.
Amit
February 1, 2007 at 1:51 am
thanks, Ninja's_RGR'us and Amit Kumar, for your kindly reply.
i try to do my query like this:
Select
a.ShipName,a.FishingDate,a.FishingLocation,
case when b.FishSpecies = 'Jack' THEN b.CatchNumber ELSE NULL END as Jack,
case when b.FishSpecies = 'Rose' THEN b.CatchNumber ELSE NULL END as Rose
from dbo.TABLE1 as a join dbo.TABLE2 as b
on a.ShipName = b.ShipName
and a.FishingDate = b.FishingDate
but the outcome goes :
ShipName FishingDate FishingLocation Jack Rose
----------------------------------------------
Titanic 1/1/2007 Northpole 1 null
Titanic 1/1/2007 Northpole null 2
Titanic 2/1/2007 Northpole 2
and i still cannt make the data of same FishingDate merged.
am i getting it wrong ?
February 1, 2007 at 2:48 am
You need to SUM the CatchNumber. Something like the following should work:
SELECT A.ShipName, A.FishingDate, A.FishingLocation, D.Jack, D.Rose
FROM TABLE1 A
JOIN (
SELECT B.ShipName
,B.FishingDate
,SUM(CASE B.FishSpecies WHEN 'Jack' THEN CatchNumber END) AS Jack
,SUM(CASE B.FishSpecies WHEN 'Rose' THEN CatchNumber END) AS Rose
FROM TABLE2 B
GROUP BY ShipName, FishingDate) D
ON A.ShipName = D.ShipName AND A.FishingDate = D.FishingDate
ORDER BY A.ShipName, A.FishingDate
February 1, 2007 at 6:18 pm
many thanks,Ken!
why didnt i come up with this. orz...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy