July 18, 2014 at 9:05 am
The attached image is a small result set from a table in a database I have. To give an example of what I am looking for, I want a list of what I would refer to as the fully qualified name of what each bed would be. The result set should include a total of 6 lines:
ENGINTLAB:CCU:CCU1:0
ENGINTLAB:CCU:CCU1:1
ENGINTLAB:CCU:CCU1:100ENGINTLAB:CCU:CCU2:0
ENGINTLAB:CCU:CCU2:1
ENGINTLAB:CCU:CCU2:100
To explain what this data is and make it easier, think of this like a mock hospital. The Location_Id column is the PK, The Parent_Location_Id column is a reference link to the Location_Id, Location type represents 4 different possible types (F = Name of Facility, A = Area of the Hospital, R = Patient Room within that Area, B = Bed within that Room). There will never be more than one Facility.
As I was searching through the internet, I saw many references to Coalesce. That seems like what I need, but the websites with examples I see reference using one column as the "Primary" column (hope I am explaining that right), but I have two, possibly three that would need to be referenced. It seemed to me that if I only wanted room and bed those would work, but I want the Area and Facility as well.
Any ideas would be appreciated. Thank you.
July 18, 2014 at 10:27 am
ken_cox (7/18/2014)
The attached image is a small result set from a table in a database I have. To give an example of what I am looking for, I want a list of what I would refer to as the fully qualified name of what each bed would be. The result set should include a total of 6 lines:ENGINTLAB:CCU:CCU1:0
ENGINTLAB:CCU:CCU1:1
ENGINTLAB:CCU:CCU1:100ENGINTLAB:CCU:CCU2:0
ENGINTLAB:CCU:CCU2:1
ENGINTLAB:CCU:CCU2:100
To explain what this data is and make it easier, think of this like a mock hospital. The Location_Id column is the PK, The Parent_Location_Id column is a reference link to the Location_Id, Location type represents 4 different possible types (F = Name of Facility, A = Area of the Hospital, R = Patient Room within that Area, B = Bed within that Room). There will never be more than one Facility.
As I was searching through the internet, I saw many references to Coalesce. That seems like what I need, but the websites with examples I see reference using one column as the "Primary" column (hope I am explaining that right), but I have two, possibly three that would need to be referenced. It seemed to me that if I only wanted room and bed those would work, but I want the Area and Facility as well.
Any ideas would be appreciated. Thank you.
Looks like a recursive CTE would get you what you want. Without the DDL (CREATE TABLE statement) for the table, sample data in the form of INSERT INTO statements not much more we can do.
July 18, 2014 at 10:28 am
1. You should post test data in a consumable format:
CREATE TABLE #t
(
Location_Id int NOT NULL
,Parent_Location_Id int NULL
,Location_type char(1) NOT NULL
,Location_name varchar(10) NOT NULL
);
INSERT INTO #t
VALUES(1, NULL, 'F', 'ENGINTLAB')
,(33, 1, 'A', 'CCU')
,(35, 33, 'R', 'CCU1')
,(36, 33, 'R', 'CCU2')
,(41, 35, 'B', '0')
,(42, 35, 'B', '1')
,(43, 35, 'B', '100')
,(44, 36, 'B', '0')
,(45, 36, 'B', '1')
,(46, 36, 'B', '100');
2. Recursion looks as though it will produce the required result:
WITH FullyQualified
AS
(
SELECT Location_id, Location_type, CAST(Location_name AS varchar(255)) AS Location_name
FROM #t
WHERE Parent_Location_Id IS NULL
UNION ALL
SELECT T.Location_Id, T.Location_type
,CAST(P.Location_name + ':' + T.Location_name AS varchar(255))
FROM #t T
JOIN FullyQualified P
ON T.Parent_Location_Id = P.Location_Id
)
SELECT Location_name
FROM FullyQualified
WHERE Location_type = 'B'
ORDER BY Location_name;
July 18, 2014 at 11:13 am
I will post test data in a script next time rather than just a picture of the sample output.
It worked perfectly. Thank you for the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply