SQL Combine multiple records into a single row using multiple SELECTs - EASY?

Hello,

I've got the following output:
PtID   Primary Ins   Secondary Ins
11      Medicare       NULL              
11      NULL              BCBS              

From the following code:
SELECT [PtInsurance].[PtID]
      ,(SELECT [InsCarrier].[Name]
        FROM [InsCarrier]
        WHERE [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 1) AS 'Primary Ins'
      ,(SELECT [InsCarrier].[Name]
        FROM [InsCarrier]
        WHERE [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 2) AS 'Secondary Ins'
FROM [PtInsurance]
WHERE [PtInsurance].[PtID] = 11

I'm trying to get:
PtID   Primary Ins   Secondary Ins
11      Medicare       BCBS              

How?  

I've tried every kind of join and order I can think of, and I just can't find a solution - but I'm guessing it's pretty easy to someone who knows what they're doing.
cotton9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramprakash JeyabalanCommented:
Use this example.
create table test
(
  Id      int,
  Name    varchar(100),
  Address varchar(100),
  City    varchar(100),
  State   varchar(100),
  Active  bit,
  Email   varchar(100),      
  Date    datetime
)
insert into test
values (1,   'Acme1',    NULL,             NULL,          NULL,    NULL,    'blah@yada.com',     '3/1/2011')
insert into test
values (2,   'Acme1',    '1234 Abc Rd',      'Springfield',   'OR',      0,       'blah@gmail.com',    '1/12/2012')
insert into test
values (3,   'Acme2',    NULL,             NULL,          NULL,    1,       'blah@yahoo.com',    '4/19/2012')

Open in new window

; with rows as (
    select test.*,
  -- Template row must be last - how do you decide which one is template row?
  -- In this case template row is the one with id = 1
    row_number() over (order by case when id = 1 then 1 else 0 end,
                       date) rn
    from test
),
positions as (
  select
    max (case when Name is not null then rn else 0 end) NamePosition,
    max (case when Address is not null then rn else 0 end) AddressPosition,
    max (case when City is not null then rn else 0 end) CityPosition,
    max (case when State is not null then rn else 0 end) StatePosition,
    max (case when Active is not null then rn else 0 end) ActivePosition,
    max (case when Email is not null then rn else 0 end) EmailPosition,
    max (case when Date is not null then rn else 0 end) DatePosition
  from rows
)
select 
  (select Name from rows cross join Positions where rn = NamePosition) name,
  (select Address from rows cross join Positions where rn = AddressPosition) Address,
  (select City from rows cross join Positions where rn = CityPosition) City,
  (select State from rows cross join Positions where rn = StatePosition) State,
  (select Active from rows cross join Positions where rn = ActivePosition) Active,
  (select Email from rows cross join Positions where rn = EmailPosition) Email,
  (select Date from rows cross join Positions where rn = DatePosition) Date
from test
where id = 1

Open in new window

0
PortletPaulfreelancerCommented:
SELECT
      [PtInsurance].[PtID]
    , MAX(pl1.[Name]) AS 'Primary Ins'
    , MAX(pl2.[Name]) AS 'Secondary Ins'
FROM [PtInsurance]
      LEFT JOIN [InsCarrier] AS pl1 ON [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 1
      LEFT JOIN [InsCarrier] AS pl2 ON [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 2
WHERE [PtInsurance].[PtID] = 11
GROUP BY
      [PtInsurance].[PtID]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
Hi!

How about something like this

SELECT [PtInsurance].[PtID]
      ,(SELECT [InsCarrier].[Name]
        FROM [InsCarrier]
        WHERE [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 1 for XML PATH('')) AS 'Primary Ins'
      ,(SELECT [InsCarrier].[Name]
        FROM [InsCarrier]
        WHERE [InsCarrier].[ID] = [PtInsurance].[InsCarrierID] AND [PtInsurance].[PayerLevel] = 2 for XML PATH('')) AS 'Secondary Ins'
FROM [PtInsurance]
WHERE [PtInsurance].[PtID] = 11

Open in new window


Regards,
    Tomas Helgi
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
You need to give us a description of your input.  I can't tell if you are looking at one record or multiples.  Can there be more than one primary and one secondary?
0
Scott PletcherSenior DBACommented:
Typically you'll get much better performance by GROUPing all data in the secondary table at once, rather than a single column at a time:

SELECT p.PtID, ic.[Primary Ins], ic.[Secondary Ins]
FROM PtInsurance p
LEFT OUTER JOIN (
    SELECT ID,
        MAX(CASE WHEN PayerLevel = 1 THEN Name ELSE '' END) AS 'Primary Ins',
        MAX(CASE WHEN PayerLevel = 2 THEN Name ELSE '' END) AS 'Secondary Ins'
    FROM InsCarrier
    WHERE PayerLevel IN (1, 2)
    GROUP BY ID
) AS ic ON ic.ID = p.InsCarrierID
0
cotton9Author Commented:
Thank you all for the help.  I can't test anything until I get access to that PC this weekend.  But I'll test, report back, and accept a solution shortly.  

For the archives, and to answer PatHartman's question:
When I query the table using a single Patient ID, I get one or two records (usually two) - insurances affiliated with that patient.  There is always one primary and no more than one secondary.
0
cotton9Author Commented:
Thanks!  We had to change Line 6 and 7 slightly, but this solution worked well.  (Others may also - did not test)

SQL Server didn't like "pl1 ON [InsCarrier].[ID]".  Had to change it to pl1 ON pl1.[ID]
0
PortletPaulfreelancerCommented:
oops, yes, I forgot to substitute the aliases. well spotted. cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.