CODEWITHSUNDEEP
sqlsql-server
mat1986
mat1986

Reputation: 135

SQL Take two row values and transpose into two columns on one row

I'm trying to turn 2 rows into one by taking the different values in each row and transposing them into multiple columns, for example...

planID | jobNumber | qty | Name
  1    |    1      | 100 | abc
  1    |    2      | 100 | def

I want it to look like...

planID | jobNumber | qty | Name1 | Name2
  1    |    1      | 100 | abc   |  def

My query is a follows...

 SELECT DISTINCT s.planID, s.jobNumber, s.quantity, p.Name                  
              FROM schedule AS s
              JOIN partReferences as p ON p.yNumber = s.yNumber

I've tried using a pivot and case statement but can't seem to separate the name column properly

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can use conditional aggregation. Something like this:

SELECT s.planID, MIN(s.jobNumber) as jobNumber,
       MIN(s.quantity) as quantity,
       MAX(CASE WHEN s.jobNumber = 1 THEN p.Name END) as name_1,                
       MAX(CASE WHEN s.jobNumber = 2 THEN p.Name END) as name_2                
FROM schedule s JOIN
     partReferences p
     ON p.yNumber = s.yNumber
GROUP BY s.planID;

The logic for the second and third columns is not clear in the question, but this should return the result set you have specified.

Upvotes: 1

Related Questions