Convert into Flat Table

Question

Post reply

Convert into Flat Table

Galahad Phillips

Mr or Mrs. 500

Points: 551
More actions
March 2, 2006 at 9:39 am

#111993

I have the following two tables:
ID Type
1   Division
2   District
3   Facility
NameID ID NameDescription
010101 1   Nursing - Central
010102 1   Nursing - North
01        2  Central Indiana
02        2  North Texas
0110     3  Manor Care
o111     3 Hills Care
I'm trying to determine a way to turn these records into a flat table or view so the final result looks like this:
FacilityID,FacilityName,DistrictID,DistrictName,DivisionID,DivisionName
0110       ManorCare     01    Central Indiana 010101   Nursing - Central
0111       Hills Care       02    North Texas      010102   NursingNorth
Any ideas?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Perhaps you should provide additional information about your data example.

Is the NameID supposed to correlate to some kind of hierarchy?

and how does that work?

This perhaps could end up being a modeling issue.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Galahad,

Ray's right that you need to post more information about your problem. How do you know, for example, that rows 1, 3 and 5 (or 2, 4 and 6) in your example relate to one another?

In the simplest case, you could do something like this (note that I've added 'MyId' to relate some of the rows)...

CREATE TABLE #T1 (ID INT, Type VARCHAR(20))

INSERT INTO #T1

SELECT 1, 'Division' UNION

SELECT 2, 'District' UNION

SELECT 3, 'Facility'

CREATE TABLE #T2 (MyId INT, NameID VARCHAR(10), ID INT, NameDescription VARCHAR(30))

INSERT INTO #T2

SELECT 1, '010101', 1, 'Nursing - Central' UNION

SELECT 2, '010102', 1, 'Nursing - North' UNION

SELECT 1, '01', 2, 'Central Indiana' UNION

SELECT 2, '02', 2, 'North Texas' UNION

SELECT 1, '0110', 3, 'Manor Care' UNION

SELECT 2, '0111', 3, 'Hills Care'

SELECT FacilityID, FacilityName, DistrictID, DistrictName, DivisionID, DivisionName FROM

(SELECT MyId, NameID AS 'FacilityId', NameDescription AS 'FacilityName' FROM #T2 WHERE ID = 3) a,

(SELECT MyId, NameID AS 'DistrictId', NameDescription AS 'DistrictName' FROM #T2 WHERE ID = 2) b,

(SELECT MyId, NameID AS 'DivisionId', NameDescription AS 'DivisionName' FROM #T2 WHERE ID = 1) c

WHERE a.MyId = b.MyId AND a.MyId = c.MyId

DROP TABLE #T1

DROP TABLE #T2

...but I'm guessing you don't have the simplest case.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Galahad Phillips Mr or Mrs. 500 Points: 551 More actions · Answer 3

You're right it's not the simplest case

In response to Ray's question the NameID is just a reference to actual ID number of the record.

In regards to his other question about how do i know the relationship, I simply put in the values where I know that they are supposed to exist.

So based off of what I have in those two tables, is there enough info to create the flat table?

It doesn't seem like it to me...

In the case of the Hills Care record, a NameID value of 0111 would translate to what is called a FacilityID.

In the case of the Central Indiana record, a NameID value of 0111 would translate to what is called a DistrictID.

etc...etc...

There a hundreds of records containing the facility, district and division info as shown in the second table...