CODEWITHSUNDEEP
sql-server
SWaG
SWaG

Reputation: 23

Trying to Pivot one column based on the value in another

I have the following values that I'm trying to pivot into one row.

----------------------------------------------
EmplID |    SSN      |     source   |Code    |
1234   | 111-11-1111 |  L           | AB123  |
1234   | 111-11-1111 |  E           | ZY987  |
9876   | 222-22-2222 |  L           | CD456  |
9876   | 222-22-2222 |  E           | XW789  |
----------------------------------------------

I want to have the output look like this:

------------------------------------------------
Empl_ID  |    ssn      |  LCode     |   ECode  |
1234     |  111-11-1111|  AB123     |   ZY987  |
9876     |  222-22-2222|  CD456     |  XW789   |

Any ideas?

Sorry if the formatting above looks wonky

Thanks!

Upvotes: 2

Views: 28

Answers (1)

Martin Smith
Martin Smith

Reputation: 453573

You can do this straightforwardly as below

SELECT Empl_ID,
       ssn,
       MAX(CASE WHEN source = 'L' THEN Code END) AS LCode,
       MAX(CASE WHEN source = 'E' THEN Code END) AS ECode
FROM   YourTable
GROUP  BY Empl_ID,
          ssn

Or using PIVOT

SELECT EmplID,
       SSN,
       L AS LCode,
       E AS ECode
FROM   (SELECT EmplID,
               SSN,
               source,
               Code
        FROM   YourTable) AS PS /*So still works as desired if other columns added*/
PIVOT (MAX(Code) FOR source IN (L, E)) P

Upvotes: 2

Related Questions