Create a matrix view from a table

Question

Create a matrix view from a table

ashatimjohn

SSC Veteran

Points: 247
More actions
January 21, 2022 at 5:02 pm

#3975707

Hi,
I have a table called facility:
id name
123 Hospital blah blah
124 Dr's office 1
125 Dr's Office 2
126 Hospital 2
It joins on a table called patient
patient_id facitility_id
1234 124
1234 236
2345 236
2345 124
1235 126
1235 124
1246 123
1246 124
1246 126
I need to come up with a query that joins the two tables together to pull patient_id, facility_id, name from these 2 tables.
The patient_id can have one or more facilities. There is no fixed amount they can be associated with
I need a query to display the data like this:

I know this query is not correct, I just don't know how to make this the way I need it to display:
select patient_id facility_id name as name1, name as name2, name as name3
from patient p
left outer join facility f
on p.facility_id = f.id

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Not sure if it is just me or not, but I don't see what the data is supposed to look like...

BUT based on what you posted, I am thinking that if you provide DDL and sample data and expected output you will get more replies.

But based on your query, my GUESS is that you want to map up multiple facility ID's to a single patient ID. But then looking at the query you have, I am not sure that is correct either... The reason being you are pulling in the "facility_id" in your final query. If I assume that is a typo, then I would GUESS you want something like:

WITH cte AS (
SELECT patient_id, facility_id, ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY facility_id) AS RN
)
SELECT patient.patient_id, Fac1.name as Name1, Fac2.name as Name2, Fac3.name as Name3
FROM cte as Patient
JOIN facility AS Fac1
ON Patient.facility_id = Fac1.facility_id and Patient.RN = 1
JOIN facility AS Fac2
ON Patient.facility_id = Fac2.facility_id and Patient.RN = 2
JOIN facility AS Fac3
ON Patient.facility_id = Fac3.facility_id and Patient.RN = 3

NOTE - as no DDL or consumable sample data was provided, I didn't test the above. Plus, since I don't know the expected output, I am not sure if the above gives the expected values. I am JUST guessing. This is also not a very efficient solution as you are doing multiple joins on the same table. I imagine others will have more efficient solutions.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ashatimjohn SSC Veteran Points: 247 More actions · Answer 2

I'll work on providing DDL, I just came up with a quick example because I can't share the actual ddl. I'll have to create a test example this weekend and I'll script it out with data results because clearly I have not done a good job explaining this.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Mr. Brian Gale wrote:

Not sure if it is just me or not, but I don't see what the data is supposed to look like...

It's not just you. When the op comes back and fixes their own request for help, maybe we can help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

Hi,

I have a table called facility:

id name

123 Hospital blah blah

124 Dr's office 1

125 Dr's Office 2

126 Hospital 2

It joins on a table called patient

patient_id facitility_id

1234 124

1234 236

2345 236

2345 124

1235 126

1235 124

1246 123

1246 124

1246 126

>> I need to come up with a query that joins the two tables together to pull patient_id, facility_id, name [sic: name of what?] From these 2 tables.

The patient_id can have one or more facilities. There is no fixed amount they can be associated with <<

You fail post DDL. How do you expect us to help you?

>> I need a query to display the data like this: <<

Let's go back to the first week of RDBMS classes. Our model is based on a tiered architecture in which each tiered is a particular job. That means display is done separately from database queries.

>> I know this query is not correct, I just don't know how to make this the way I need it to display: <<

Your attempt at DML is wrong on several levels. Patients are a table with more than one entry; the name of such a table should be some plural or collective noun. The same way with the facilities. Next, please read any book on normal forms. In particular, you seem to have a repeating group that would violate First Normal Form(1NF)

You also never use a table to represent both a set of entities and a relationship. That means we need a table called "roster" or something like that that relates the dates and times that a patient was in one particular facility. Newbies seem to love to overuse the left outer join to make up for their bad DDL and lack of proper modeling of relationships.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

Sound like this to me?:

;WITH cte_pat_ordered_by_fac AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY p.patient_id ORDER BY p.facility_id) AS row_num
    FROM patient p
    INNER JOIN facility f ON p.facility_id = f.id
)
SELECT patient_id,
    MAX(CASE WHEN row_num = 1 THEN facility_id END) AS facility1,
    MAX(CASE WHEN row_num = 1 THEN name END) AS name1,
    MAX(CASE WHEN row_num = 2 THEN facility_id END) AS facility2,
    MAX(CASE WHEN row_num = 2 THEN name END) AS name2,
    MAX(CASE WHEN row_num = 3 THEN facility_id END) AS facility3,
    MAX(CASE WHEN row_num = 3 THEN name END) AS name3,
    MAX(CASE WHEN row_num = 4 THEN facility_id END) AS facility4,
    MAX(CASE WHEN row_num = 4 THEN name END) AS name4,
    MAX(CASE WHEN row_num = 5 THEN facility_id END) AS facility5,
    MAX(CASE WHEN row_num = 5 THEN name END) AS name5,
    MAX(CASE WHEN row_num = 6 THEN facility_id END) AS facility6,
    MAX(CASE WHEN row_num = 6 THEN name END) AS name6,
    MAX(CASE WHEN row_num = 7 THEN facility_id END) AS facility7,
    MAX(CASE WHEN row_num = 7 THEN name END) AS name7,
    MAX(CASE WHEN row_num = 8 THEN facility_id END) AS facility8,
    MAX(CASE WHEN row_num = 8 THEN name END) AS name8,
    MAX(CASE WHEN row_num = 9 THEN facility_id END) AS facility9,
    MAX(CASE WHEN row_num = 9 THEN name END) AS name9
FROM cte_pat_ordered_by_fac
GROUP BY patient_id
ORDER BY patient_id

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".