skip to Main Content

I have table data after doing all joins and restrictions like this:

Name Type Path
Item 1 mobile /mobile/image1.jpg
Item 1 desktop /desktop/image1.jpg
Item 2 mobile /mobile/image2.jpg
Item 2 desktop /desktop/image2.jpg

I want the result table to look like this in the end:

Name Path mobile Path desktop
Item 1 /mobile/image1.jpg /desktop/image1.jpg
Item 2 /mobile/image2.jpg /desktop/image2.jpg

What SQL (mySQL) feature can be used to get such a result?

So far, I’ve had to do data processing (mapping) in the code that calls this request.

2

Answers


  1. We can use conditional aggregation here:

    SELECT
        Name,
        MAX(CASE WHEN Type = 'mobile'  THEN Path END) AS Path_Mobile,
        MAX(CASE WHEN Type = 'desktop' THEN Path END) AS Path_Desktop
    FROM yourTable
    GROUP BY Name
    ORDER BY Name;
    
    Login or Signup to reply.
  2. SELECT  DISTINCT(MT.[Name]),
            MT2.[Path] AS DesktopPath,
            MT3.[Path] AS MobilePath
    FROM    MyTable MT
    INNER JOIN MyTable MT2
            ON MT2.[Name] = MT.[Name] AND MT2.[Type] = 'DESKTOP'
    INNER JOIN MyTable MT3
            ON MT3.[Name] = MT.[Name] AND MT3.[Type] = 'MOBILE'
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search