0

I have a table named "initial" formatted like the following:

id category value
1 Name Bob
1 Count 102
1 Serial 123ABC
2 Name Mary
2 Serial 234BCD
3 Serial 345CDE

A table "serials":

serial color
123ABC Blue
234BCD Red
345CDE Blue

And a table 'users':

id priority
1 High
2 Medium
3 Low

I transpose the "initial" using the following using the following query, then try to join on "serial":

SELECT
MAX(CASE WHEN initial.category = 'Name' THEN initial.value END) 'Name', 
MAX(CASE WHEN initial.category = 'Count' THEN initial.value END) 'Count', 
MAX(CASE WHEN initial.category = 'Serial' THEN initial.value END) 'Serial', 
serials.color
FROM initial
LEFT JOIN users on users.id = initial.id
LEFT JOIN serials on serials.serial = initial.value

When I do this, it cannot properly join on 'serial' if there are other entries of the same id preceding it. I tried

LEFT JOIN serials on serials.serial = initial.value and initial.category = "Serial" 

but it did not do anything. If I add the and initial.category = "Serial" to the first join:

LEFT JOIN users on users.id = initial.id and initial.category = "Serial"

I can then successfully get the 'color,' but no longer get the other category values. How would I be able to successfully join onto the serials table?

Expected results:

id Name Count Serial Color
1 Bob 102 123ABC Blue
2 Mary 234BCD Red
3 345CDE Blue

Actual ('Color' isn't pulled from ids 1 and 2 because initial.value refers to the first entry of each id, in this case 'Name' "Bob" and "Mary" while for id 3, it refers to 'Serial' "345CDE":

id Name Count Serial Color
1 Bob 102 123ABC
2 Mary 234BCD
3 345CDE Blue

I thought of creating a temporary table, but due to access permissions I am unable to create any tables/views

2
  • Not sure why the second and third tables aren't working, they look fine on the preview
    – Ytg
    Commented Oct 24, 2023 at 18:01
  • Added expected results and fixed formatting
    – Ytg
    Commented Oct 24, 2023 at 21:22

1 Answer 1

0

You can perform transposing as a subquery, and then do your joins:

SELECT 
   inpvtd.id,
   inpvtd.'Name',
   inpvtd.'Count',
   inpvtd.'Serial',
   s.Color

FROM (
    SELECT
      init.id,
      MAX(CASE WHEN init.category = 'Name' THEN init.value END) 'Name', 
      MAX(CASE WHEN init.category = 'Count' THEN init.value END) 'Count', 
      MAX(CASE WHEN init.category = 'Serial' THEN init.value END) 'Serial'
    FROM initial init
    GROUP BY 
      init.id
    ) inpvtd
   
  LEFT JOIN users u
  on u.id = inpvtd.id

  LEFT JOIN serials s
  on s.serial = inpvtd.Serial

1
  • Worked great, thank you!
    – Ytg
    Commented Oct 26, 2023 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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