0

I have a table as shown below

+-----+------- ---+------------+------------+
| Id  | Location  | DateIn     | Dateout    |
+-----+-----------+------------+------------+
| 117 | SHIP      | 2021-09-01 | 2021-09-05 |
| 117 | PORT      | 2021-09-05 | 2021-09-13 |
| 118 | SHIP      | 2021-09-04 | NULL       |
| 119 | SHIP      | 2021-09-04 | 2021-09-08 |
| 119 | PORT      | 2021-09-08 | 2021-09-10 |
| 120 | SHIP      | 2021-09-10 | NULL       |
+-----+-----------+------------+------------+

my desired output is like this

+-----+------- ----+------------+------------+------------+
| Id  | SHIP-IN    | SHIP-OUT   | PORT-IN    | PORT-OUT   |
+-----+------------+------------+------------+------------+
| 117 | 2021-09-01 | 2021-09-05 | 2021-09-05 | 2021-09-13 |
| 118 | 2021-09-04 | NULL       | NULL       | NULL       |
| 119 | 2021-09-04 | 2021-09-08 | 2021-09-08 | 2021-09-10 |
| 120 | 2021-09-10 | NULL       | NULL       | NULL       |
+-----+------------+------------+------------+------------+

the code I created is like this

SELECT
*
FROM
(
  SELECT 
   Id,
   [Location] As Category,
   [Location]+'1' As Category1,
   DateIn, 
   DateOut
  FROM tblLocation
 ) AS P

 PIVOT
 (
   max(DateIn) FOR Category IN ([SHIP], [PORT])
 ) AS pv1

 PIVOT
 (
   max(DateOut) FOR Category1 IN ([SHIP1], [PORT1])
 ) AS pv2

 GO

and the result i got is

+-----+------------+------------+------------+------------+
| Id  | SHIP       | PORT       | SHIP1      | PORT1      |
+-----+------------+------------+------------+------------+
| 117 | 2021-09-01 | NULL       | 2021-09-05 | NULL       |
| 117 | NULL       | 2021-09-05 | NULL       | 2021-09-13 |
| 118 | 2021-09-04 | NULL       | NULL       | NULL       |
| 119 | 2021-09-04 | NULL       | 2021-09-08 | NULL       |
| 119 | NULL       | 2021-09-08 | NULL       | 2021-09-10 |
| 120 | 2021-09-10 | NULL       | NULL       | NULL       |
+-----+------------+------------+------------+------------+

how to fix this issue? please help

0
2

for pivoting for multiple columns, easiest way is to use CASE expression

SELECT Id,
       MAX (CASE WHEN [Location] = 'SHIP' THEN DateIn  END) AS [SHIP-IN],
       MAX (CASE WHEN [Location] = 'SHIP' THEN DateOut END) AS [SHIP-OUT],
       MAX (CASE WHEN [Location] = 'PORT' THEN DateIn  END) AS [PORT-IN],
       MAX (CASE WHEN [Location] = 'PORT' THEN DateOut END) AS [PORT-OUT]
FROM   tblLocation
GROUP BY Id
1

The easiest way is to use the SUM with a CASE expression (as answered by Squirrel), but you can do it if you unpivot the data first. See, you have one too many categories to pivot the data without introducing null values.

DECLARE @Ship TABLE
    (
    ID INT NOT NULL
    , Location NVARCHAR(50) NOT NULL
    , DateIn DATE NULL
    , DateOut DATE NULL
    )

INSERT INTO @Ship 
    (ID, Location, DateIn, DateOut)
VALUES ('117', 'SHIP', '2021-09-01', '2021-09-05')
, ('117', 'PORT', '2021-09-05', '2021-09-13')
, ('118', 'SHIP', '2021-09-04', NULL)
, ('119', 'SHIP', '2021-09-04', '2021-09-08')
, ('119', 'PORT', '2021-09-08', '2021-09-10')
, ('120', 'SHIP', '2021-09-10', NULL)

;WITH CTE_UP AS
    (
    SELECT ID
        , DateValue
        , LocComposite = Location + CASE WHEN DateCategory = 'DateIn' THEN '-IN' ELSE '-OUT' END
    FROM @Ship AS S
        UNPIVOT (DateValue FOR DateCategory IN (DateIn, DateOut)) AS unpvt
    )
, CTE_P AS
    (
    SELECT ID
        , [SHIP-IN]
        , [SHIP-OUT]
        , [PORT-IN]
        , [PORT-OUT]
    FROM CTE_Up 
        PIVOT (MAX(DateValue) FOR LocComposite IN ([SHIP-IN], [SHIP-OUT], [PORT-IN], [PORT-OUT])) AS pvt 
    )
SELECT * FROM CTE_P

Not the answer you're looking for? Browse other questions tagged or ask your own question.