Turn two tables and a link table into one big table

Question

I tried looking for this question, but found nothing relevant. So here goes...

I have three tables, the extremely simplified versions of which are:

Practitioners:

• practitioner_id :: int
• name :: nvarchar

Insurances:

• insurance_id :: int
• name :: nvarchar

InsuranceLink:

• practitioner_id :: int
• insurance_id :: int

So, the practitioner table contains a list of practitioners, the insurance table contains a list of insurances, and the link table represents which practitioner supports which insurance.

Now, I need to create a view which can display the information like this:

ViewTable:

• practitioner_id :: int
• practitioner_name :: nvarchar
• insurance_1 :: bit
• insurance_2 :: bit .....
• insurance_100 :: bit

In other words, the columns in the view are the ID and name of the practitioner, and every insurance that exists in Insurances (with the insurance name as the column name (there is an enforced condition that insurance names are unique)). The cells in the insurance columns will indicate if that practitioner supports that insurance.

Is there any way of doing this?

Or better yet, is it possible to use an excel-style pivot table on a DataSet in a VB.NET form? That would also solve many of my problems.

Answer 1

PIVOT is the keyword you're looking for.

select * from 
   (select 
        practitioner_id, 
        practitioner_name, 
        insurance_name
    from practitioner p
    join insurancelink il on p.practitioner_id = il.practitioner_id
    join insurance i on il.insurance_id = i.insurance_id
   )
pivot (count(*) for insurance_name in ([insurancename1],[insurancename2], ..., [insurancename100]))

I know that now you're wondering if there's any way to avoid listing out all the insurance names/having it not be a constant list. The answer is no, pretty much. You can create the view programatically but the view is still going to need to be updated when an insurance is added.