how to denormalize in query?

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

Amit Kumar Mr or Mrs. 500 Points: 564 More actions · Answer 2

Hi

Its better to use inner join to avoid any manual addition in your query.

Amit

mohawk SSC Enthusiast Points: 151 More actions · Answer 3

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 ?

Ken McKelvey SSCoach Points: 18775 More actions · Answer 4

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

mohawk SSC Enthusiast Points: 151 More actions · Answer 5

many thanks,Ken!

why didnt i come up with this. orz...