Search code examples
sqlsql-serverunpivotdbeaver

Unpivot Data in Dbeaver using query

I have below set of data where the ship from and ship to location for a tracking ID is mentioned in two different row -

For ID 1234 CA is the ship from City and WA is the ship to City

ID Type City State
1234 From CA ABC
1234 To WA XYZ

I want to create a new table to have the Ship from location and Ship To City in the same row like below

ID From City From State To City To State
1234 CA ABC WA XYZ

Can someone help what should be the syntax to achieve this result ?

Solution

  • Just do aggregation with condition:

    SELECT ID
      ,MAX(CASE WHEN Type = 'From' THEN City END)
      ,MAX(CASE WHEN Type = 'From' THEN State END)
      ,MAX(CASE WHEN Type = 'To' THEN City END)
      ,MAX(CASE WHEN Type = 'To' THEN State END)
FROM #MyTable 
GROUP BY ID