CODEWITHSUNDEEP
sqloracle-database
RLearner
RLearner

Reputation: 41

Splitting value to two columns in SQL

I have a table that stores the VIN numbers and delivery dates of vehicles based on a code. I want to be able to get one row with three columns of data.

I have tried the following 

SELECT DISTINCT VIN, MAX(TRANSACTION_DATE) AS DELIVERY_DATE
  FROM "TABLE"
 WHERE DELIVERY_TYPE ='025'
   AND VIN IN ('XYZ')
 GROUP BY VIN
UNION ALL
SELECT VIN, MAX(TRANSACTION_DATE) AS OTHER_DELIVERY_DATE
  FROM "TABLE"
 WHERE DELIVERY_TYPE !='025'
   AND VIN IN ('XYZ')
 GROUP BY VIN;

When I run this I get

 VIN  DELIVERY_DATE
 XYZ    26-dec-18
 XYZ    01-MAY-19

current data format in table:

 VIN  TRANSACTION_DATE
 XYZ      26-DEC-18
 XYZ      01-MAY-19

Required format:

 VIN   DELIVERY_DATE   OTHER_DELIVERY DATE
 XYZ      26-DEC-18           01-MAY-19

Upvotes: 2

Views: 53

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use conditional aggregation

SELECT  VIN,
        MAX (CASE WHEN DELIVERY_TYPE ='025' AND
                       VIN IN ('XYZ') then TRANSACTION_DATE end) AS DELIVERY_DATE
        MAX(CASE WHEN DELIVERY_TYPE !='025' AND
                       VIN IN ('XYZ') then TRANSACTION_DATE end) AS OTHER_DELIVERY
  FROM "TABLE"     
 GROUP BY VIN

Upvotes: 1

tgralex
tgralex

Reputation: 814

You can use CROSS APPLY

DECLARE @Cars TABLE (VIN VARCHAR(100), DELIVERY_TYPE VARCHAR(3), TRANSACTION_DATE DATE)
INSERT INTO @Cars
(VIN, DELIVERY_TYPE , TRANSACTION_DATE)
VALUES
('XYZ', '025', '20181226'), ('XYZ', '030', '20190319')

I needed above code to be able to run without a table and data, all you need is this:

SELECT DISTINCT C.VIN, DD.DELIVERY_DATE, TD.TRANSACTION_DATE
    FROM @Cars C
    CROSS APPLY (SELECT MAX(TRANSACTION_DATE) DELIVERY_DATE FROM @Cars D WHERE D.DELIVERY_TYPE = '025' AND D.VIN = C.VIN) DD
    CROSS APPLY (SELECT MAX(TRANSACTION_DATE) TRANSACTION_DATE FROM @Cars D WHERE D.DELIVERY_TYPE = '025' AND D.VIN = C.VIN) TD

If you need to transpond not two but a lot more columns, I'd suggest using PIVOT TABLE as more appropriate, but for two columns either CROSS APPLY or conditional aggregation will do the trick.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use conditional aggregation:

SELECT VIN,
       MAX(CASE WHEN DELIVERY_TYPE = 25 THEN TRANSACTION_DATE END) AS DELIVERY_DATE,
       MAX(CASE WHEN DELIVERY_TYPE <> 25 THEN TRANSACTION_DATE END) AS TRANSACTION_DATE
FROM TABLE
WHERE VIN IN ('XYZ')
GROUP BY VIN;

Note that SELECT DISTINCT is almost never used with GROUP BY.

Upvotes: 0

Related Questions