CODEWITHSUNDEEP
sql-server
jackstraw22
jackstraw22

Reputation: 641

SQL populate new column based on criteria from another column

I have a table where a doctor can work in multiple locations -- one while the others are not. Each location is in a state. I want to create a column that shows the state of the primary location for that doctor. So if a location is not primary, then I want to see the state for the primary location.

I'm trying to create the PrimaryLocationState column but I can't seem to get this.

    declare @t table (doctorid int,
                      primarylocation char,
                      state varchar(2)
                      )

    insert into @t 
    values (1, 'Y', 'FL'), (1, 'N', 'GA'),
           (2, 'Y', 'TX'), (2, 'N', 'CA');

I'm trying to get this result:

    DoctorId      PrimaryLocation     State     PrimaryLocationState
    -----------------------------------------------------------------
      1              Y                  FL              FL
      1              N                  GA              FL
      2              Y                  TX              TX
      2              N                  CA              TX

Upvotes: 1

Views: 915

Answers (3)

JamieD77
JamieD77

Reputation: 13949

You can use a correlated subquery, or OUTER APPLY to get the Primary Location 

select  *
from    @t t1
outer apply (
    select  state as PrimaryLocationState
    from    @t t2
    where   t1.doctorid = t2.doctorid
    and     primarylocation = 'Y'
) pl

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 82010

Example

Select *
      ,PrimaryLocationState =max(case when primarylocation='Y' then state end) over (partition by doctorid) 
 from @t

Returns

doctorid    primarylocation state   PrimaryLocationState
1           Y               FL      FL
1           N               GA      FL
2           Y               TX      TX
2           N               CA      TX

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

You need a INNER JOIN

First you need found out the Primary Location for each doctor.

SELECT DoctorID, state as PrimaryLocationState
FROM yourTable
WHERE primarylocation = 'Y';

Then add that information to each row

SELECT t.*, P.PrimaryLocationState
FROM YourTable t
INNER JOIN ( SELECT DoctorID, state as PrimaryLocationState
             FROM yourTable
             WHERE primarylocation = 'Y') P
   t.DoctorID = p.DoctorID

Upvotes: 1

Related Questions