My scenario is below:
I have a profile table as below
ProfileID int
FirstName varchar(50)
LastName varchar(100)
DateOfBirthDay varchar(100)
DateOfBirthMonth varchar(100)
DateOfBirthYear varchar(100)
Gender varchar(100)
Address1 varchar(100)
Address2 varchar(100)
City varchar(100)
Province varchar(100)
PostalCode varchar(100)
PhoneNumber varchar(100)
WorkNumber varchar(100)
CellNumber varchar(100)
EmailAddress varchar(100)
I also have table to store the different conditions for each profile as below:
ProfileId int
CurrentWeight float
HeightUnit varchar(50)
CurrentHeight float
Occupation varchar(50)
Conditions varchar(MAX)
Medications varchar(MAX)
Is_Pregnant bit
Diseases varchar(MAX)
Allergy_Food varchar(MAX)
Supplements_Taken varchar(MAX)
Programs_Attended varchar(MAX)
Knowledge_Source varchar(MAX)
DateOfPhysicalExam datetime
MoreInformation varchar(MAX)
Data is stored for each record in this table as below:(sample)
False 10,12, 119, 138, 145,147,149,
It just stores the id of the conditions ids as comma separated values. There is a master table which stores the value associated with each id as below:
5 1 Conditions Organ transplant
6 1 Conditions Recent heart attack (within the last 6 months) A
7 1 Conditions Recent stroke (within the last 6 months) A
8 2 Medications Adderral
9 2 Medications Aptivus Aptivus A
10 2 Medications Atripla Atripla A
11 2 Medications Chloramphenicol
12 2 Medications Coumadin
13 2 Medications Crixivan
.................................
Now I need to create a report using stored procedure to fetch each customers profile along with the conditions, medications etc.... in the format below:
id Name Age Occupation Diseases Medications Is_Preg Supplements_Taken
10001 TestName 59 Office Asthma, Blood Pressure Heparin, Aptivus, Indocin False
How do I go with this?
