CODEWITHSUNDEEP
database
nikhilthecoder
nikhilthecoder

Reputation: 299

Selecting values of a column from multiple rows of the same table as different columns

I have a Table With the following definition : 

CREATE TABLE [dbo].[Due] (
[dueID]           INT            IDENTITY (1, 1) NOT NULL,
[dueTypeID]       INT            NOT NULL,
[vehicleID]        INT            NOT NULL,
[dueDate]         DATE           NULL,
...

Here I want to generate a query which would give an output as

VehicleID  Type1.dueDate  Type2.dueDate  Type3.dueDate ...

(the due dates should be the highest for that vehicle for that due type)

I have written the following query to do this : 

select vehicleNumber, A.dueDate, B.dueDate, C.dueDate, D.dueDate, E.dueDate, F.dueDate 
FROM Vehicle INNER JOIN 
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '1' GROUP BY Due.vehicleID) As A ON Vehicle.vehicleID = A.vehicleID INNER JOIN
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '2' GROUP BY Due.vehicleID) As B ON Vehicle.vehicleID = A.vehicleID INNER JOIN
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '3' GROUP BY Due.vehicleID) As C ON Vehicle.vehicleID = A.vehicleID INNER JOIN
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '4' GROUP BY Due.vehicleID) As D ON Vehicle.vehicleID = A.vehicleID INNER JOIN
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '5' GROUP BY Due.vehicleID) As E ON Vehicle.vehicleID = A.vehicleID INNER JOIN
(SELECT Max(dueDate) as dueDate,vehicleID from Due where dueTypeID = '6' GROUP BY Due.vehicleID) As F ON Vehicle.vehicleID = A.vehicleID

The problem is, the vehicle which does not have a due date for a particular type is taking the value of some other vehicle. Please help me figure out how this can be solved, also if I am doing this the right way or whether there is some better way to do this?

(sorry in case the question is repetitive as I tried looking for it but couldn't find the right keywords to search the problem)

Thanks a lot for helping me out.

Upvotes: 1

Views: 253

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146499

Try this:

 select vehicleNumber, 
     Max(case When dueTypeID = '1' Then dueDate Else null end) Typ1dueDate, 
     Max(case When dueTypeID = '2' Then dueDate Else null end) Typ2dueDate, 
     Max(case When dueTypeID = '3' Then dueDate Else null end) Typ3dueDate, 
     Max(case When dueTypeID = '4' Then dueDate Else null end) Typ4dueDate, 
     Max(case When dueTypeID = '5' Then dueDate Else null end) Typ5dueDate, 
     Max(case When dueTypeID = '6' Then dueDate Else null end) Typ6dueDate
 FROM Due d join Vehicle v 
     On v.vehicleId = d.vehicleId 
 Group By v.vehicleNumber

Upvotes: 1

Related Questions