Question 22132 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL: split values by columns depending on condition – Mysql

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.

Tags:

2

Answers


  1. 0

    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. 0 
    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