从逗号分隔值生成报告的存储过程？

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:

它只是将条件ID的id存储为逗号分隔值。有一个主表存储与每个id关联的值,如下所示:

    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?

我该怎么做?

1 个解决方案

#1

Perhaps this T-SQL split string function will work for you.

也许这个T-SQL分裂字符串函数适合您。

You can join the table that is returned to condition/medication lookup table.

您可以加入返回到条件/药物查找表的表。

#1