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

Improve this question
0

2 Answers 2

Reset to default
3

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
Improve this answer
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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.