如何基于其外键合并两个或更多行

如何基于其外键合并两个或更多行

关注
发信
关注(28)粉丝(399)

如何基于其外键合并两个或更多行？

本文介绍了如何基于其外键合并两个或更多行？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有三个表格： A（a1，a2，a3） //这个tbl（表）可以有多个实例a1，但是它依赖于b1的原因， //每次都有一个唯一的记录 B（b1，a1，b2） // tbl C与tbl B到b1有关系。 b1也可以有多个实例，但 //有时这个表中的一些唯一记录可以绑定到B tbl中的一个记录。 C（c1，b1，c2，c3） 示例： //表B示例数据 b1 a1 b2 1 25纸 2 29铅笔 3 29 parker //表C样本数据 c1 b1 c2 c3 1 1 w long 2 2 b long 3 2 g short 4 3 v fat 说明：对于tbl B中的记录2， 从到目前为止所有事情，tbl A可以在tbl B中有多个记录，它们与tbl有区别A. Tbl B也可以在tbl C中有多个记录，但是这些多个记录必须合并到一个记录中（如果tbl C中存在重复的外键，那么应该进行合并）。 我希望我在解释我的问题上做得很好。我需要一个查询来做到这一点。任何人都可以帮助吗？ 添加＃1：为了使它更清晰，我会使用真正的情况面对。 每种药物都归入主分类，子分类和通用名称。 - 主分类 -Sub分类 - 通用名称 名称可以有多个强度：示例： -TRIMETHOPRIM 优点：100mg，200mg 有时候，你可以有一个通用名称，是两种药物和这两种药物的组合有它们的优势，这是该通用名称的强度。您也可以有多个。 示例： -SULFAMETHOXAZOLE& TRIMETHOPRIM 优点：40mg& 8mg / mL，400mg& 80mg，800mg& 160mg 因此，为了让我跟踪组合通用名称的个别优势，表。 GenericTbl（Id，Name，...） GenericDetails（Id，Gen​​ericId，...） Strenghts（Id，Gen​​ericDetailsId，Strength，Unit，DosageForm，...） 希望这有帮助。 添加＃2 我已将ids更改为整数而不是guid（uniqueidentifier） Pharmacy_GenericDrug GenericDrugID GenericDrugName DrugSubClassificationID ControlStatusID 1 TRIMETHOPRIM 12 2 2 SULFAME& TRIMETHOPRIM 4 1 Pharmacy_GenericDrugDetails GenericDrugDetailsID GenericDrugID 1 1 2 2 Pharmacy_Strengths StrengthID GenericDrugDetailsID剂量强度单位 1 1 200 mg 2 2 80 mg 3 2 8 mg / L 对于第一个＃1 GenericDrugID = 1：TRIMETHOPRIM | 12 | 200 | mg | 1 对于第二个＃2 GenericDrugID = 2：SULFAME& TRIMETHOPRIM | 4 | 80 | mg | 8 | mg / L | 1 1> GenericDrugName | DrugSubClassificationID |剂量强度|单位| ControlStatusID 2> GenericDrugName | DrugSubClassificationID |剂量强度|单位|剂量强度|单位| ControlStatusID 解决方案这是合并两行外键（打破1NF规则）。 DECLARE @StrengthID INT DECLARE @GenericDrugDetailsID INT DEClARE @DosageStrength INT DECLARE @PresentationUnitID INT DECLARE @DosageFormID INT DECLARE @RouteOfAdministrationID INT DECLARE @nCombinations INT DECLARE @maxCombinations INT DECLARE @CurrCombinationTotal INT DECLARE @CurrGenericDrugDetailsID INT DECLARE @PName VARCHAR（100） DECLARE @DName VARCHAR（100） DECLARE @DNameAbbrev VARCHAR 50） DECLARE @RName VARCHAR（100） DECLARE @GetGenericDrugStrengths CURSOR SET @nCombinations = 0 SET @CurrGenericDrugDetailsID = 0 - 获取组合的最大数量 - =============================== ================================================== ==== SET @maxCombinations =（SELECT TOP 1 COUNT（GenericDrugDetailsID）AS maxCombinations FROM Pharmacy_Strengths GROUP BY GenericDrugDetailsID ORDER BY maxCombinations desc） - ================================================== ================================= - 创建临时temp保持优势 - ===================================== ======================================== DECLARE @ tmpSQL VARCHAR（max） SET @tmpSQL ='' 如果EXISTS（SELECT * FROM tempdb.sys.tables WHERE NAME LIKE'％tmpStrengths％'） DROP TABLE＃ tmpStrengths CREATE TABLE #tmpStrengths（ StrengthID INT NOT NULL， GenericDrugDetailsID INT NOT NULL， NumberOfCombinations INT NOT NULL， DosageStrength1 INT NOT NULL， PresentationUnitID1 INT NOT NULL， PresentationUnitName1 VARCHAR（100）NOT NULL， DosageFormID1 INT NOT NULL， DosageFormName1 VARCHAR（100）NOT NULL， DosageFormNameAbbrev1 VARCHAR ）NULL， RouteOfAdministrationID1 INT NOT NULL， RouteOfAdministrationName1 VARCHAR（100）NOT NULL）; IF（@maxCombinations> 1） BEGIN DECLARE @counter int SET @counter = 1 WHILE @counter BEGIN SET @counter = @counter + 1 SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD StrengthID'+ CAST（@counter AS VARCHAR（50））+ 'INT NULL;' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD DosageStrength'+ CAST（@counter AS VARCHAR（50））+'INT NULL;' SET @tmpSQL = @ tmpSQL +'ALTER TABLE #tmpStrengths ADD PresentationUnitID'+ CAST（@counter AS VARCHAR（50））+'INT NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD PresentationUnitName'+ CAST（@counter AS VARCHAR（100））+'VARCHAR（100）NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD DosageFormID'+ CAST（@counter AS VARCHAR（50））+'INT NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD DosageFormName'+ CAST（@counter AS VARCHAR（100））+'VARCHAR（100）NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD DosageFormNameAbbrev'+ CAST（@counter AS VARCHAR（50））+'VARCHAR（50）NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationID'+ CAST（@counter AS VARCHAR（50））+'INT NULL; ' SET @tmpSQL = @tmpSQL +'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationName'+ CAST（@counter AS VARCHAR（100））+'VARCHAR（100）NULL; ' END END EXEC（@tmpSQL） - ======================== ================================================== ========== SET @tmpSQL ='' SET @GetGenericDrugStrengths = CURSOR FOR SELECT StrengthID，GenericDrugDetailsID，DosageStrength，PresentationUnitID，DosageFormID，RouteOfAdministrationID FROM Pharmacy_Strengths ORDER BY GenericDrugDetailsID ASC OPEN @GetGenericDrugStrengths FETCH NEXT FROM @GetGenericDrugStrengths INTO @StrengthID，@GenericDrugDetailsID，@DosageStrength，@PresentationUnitID，@DosageFormID，@RouteOfAdministrationID WHILE @@ FETCH_STATUS = 0 BEGIN - 获取Presentation Unit，剂量表和管理路线的值。 SELECT @PName = PresentationUnitName FROM Pharmacy_PresentationUnit WHERE PresentationUnitID = @PresentationUnitID SELECT @DName = DosageFormName，@DNameAbbrev = DosageFormNameAbbrev 从Pharmacy_DosageForm WHERE DosageFormID = @DosageFormID SELECT @RName = RouteOfAdministrationName 从Pharmacy_RouteOf管理 WHERE RouteOfAdministrationID = @RouteOfAdministrationID 如果（@GenericDrugDetailsID = @CurrGenericDrugDetailsID ） BEGIN SET @nCombinations =（@nCombinations + 1） - 立即更改临时表 SET @tmpSQL = @tmpSQL +'UPDATE #tmpStrengths SET StrengthID'+ CAST（@nCombinations AS VARCHAR（50））+'='+ CAST（@StrengthID AS VARCHAR（50））+'， DosageStrength'+ CAST（@nCombinations AS VARCHAR 50））+'='+ CAST（@DosageStrength AS VARCHAR（50））+'， PresentationUnitID'+ CAST（@nCombinations AS VARCHAR（50））+'='+ CAST（@PresentationUnitID AS VARCHAR 50））+'， PresentationUnitName'+ CAST（@nCombinations AS VARCHAR（50））+'='''+ CAST（@PName AS VARCHAR（100））+'''， DosageFormID '+ CAST（@nCombinations AS VARCHAR（50））+'='+ CAST（@DosageFormID AS VARCHAR（50））+'， DosageFormName'+ CAST '+ CAST（@DName AS VARCHAR（100））+'''， DosageFormNameAbbrev'+ CAST（@nCombinations AS VARCHAR（50））+'='''+ CAST（@DNameAbbrev AS VARCHAR 50））+'''， RouteOfAdministrationID'+ CAST（@nCombinations AS VARCHAR（50））+'='+ CAST（@RouteOfAdministrationID AS VARCHAR（50））+'， RouteOfAdministrationName' CAST（@nCombinations AS VARCHAR（50））+'='''+ CAST（@RName AS VARCHAR（100））+''' WHERE GenericDrugDetailsID ='+ CAST（@GenericDrugDetailsID AS VARCHAR ; EXEC（@tmpSQL）; END ELSE BEGIN SET @nCombinations = 1 SET @CurrGenericDrugDetailsID = @GenericDrugDetailsID; - 获取预先组合的总数 SET @CurrCombinationTotal =（SELECT COUNT（GenericDrugDetailsID）来自Pharmacy_Strengths WHERE GenericDrugDetailsID = @GenericDrugDetailsID GROUP BY GenericDrugDetailsID）; - 立即在临时表中插入 INSERT INTO #tmpStrengths（StrengthID，GenericDrugDetailsID，NumberOfCombinations， DosageStrength1，PresentationUnitID1，PresentationUnitName1，DosageFormID1， DosageFormName1，DosageFormNameAbbrev1 ，RouteOfAdministrationID1，RouteOfAdministrationName1） VALUES（@StrengthID，@GenericDrugDetailsID，@CurrCombinationTotal，@DosageStrength， @PresentationUnitID，@PName，@DosageFormID，@DName，@DNameAbbrev，@RouteOfAdministrationID，@RName） END - PRINT CAST（@StrengthID AS varchar（50））+''+ CAST（@GenericDrugDetailsID AS varchar（50））+''+ CAST（@DosageStrength AS varchar ）+ +'+ CAST（@RouteOfAdministrationID as varchar（50）） FETCH NEXT（50））+ CAST（@PresentationUnitID AS varchar（50） FROM @GetGenericDrugStrengths INTO @StrengthID，@GenericDrugDetailsID，@DosageStrength，@PresentationUnitID，@DosageFormID，@RouteOfAdministrationID END --seLECT * from #tmpStrengths CLOSE @GetGenericDrugStrengths DEALLOCATE @ GetGenericDrugStrengths SELECT a.GenericDrugID， a.GenericDrugName， f. *， a.InsertDate， a.InsertFKUserAccountId， a.UpdateDate， a.UpdateFKUserAccountId， a.Version 来自Pharmacy_GenericDrug a INNER JOIN Pharmacy_ControlStatus d ON d.ControlStatusID = a.ControlStatusID INNER JOIN Pharmacy_GenericDrugDetails e ON e.GenericDrugID = a.GenericDrugID INNER JOIN #tmpStrengths f ON f.GenericDrugDetailsID = e.GenericDrugDetailsID ORDER BY GenericDrugName ASC 这是我在查询之前得到的： GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName GenericDrugDetailsID DosageStrength PresentationUnitID PresentationUnitName DosageFormID DosageFormName DosageFormNameAbbrev RouteOfAdministrationID RouteOfAdministrationName ControlStatusID ControlStatusName InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId版本 ----------------------------- ------- ------------------------------------------- -------------------------------------------------- ------- ------------------------------------ ------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------- ------- ----------------------------- --------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------- -------------------- - ------------- ------------------ ------------------- -------------------------------------------------- ------------------------------- ------------ ------- -------------------------------------------------- ------------------------------------------- ------- ------------------------------------------- ------- ---------------- ---------------------------------- -------------------------------------------------- ---------------- --------------- ------------------- -------------------------------------------------- ------------------------------- ------------------- ---- --------------------- ----------------------- - ------------------- ----------------------- 83222B76-6690- 42F2-BDAD-BE5BD2D19D3B通用药物一8ED135ED-5FDB-419A-9C7D-E788A40EAEAC药物亚分类二61D618F3-A38A-4416-B0A4-FB29AD614B4B药物分类一2 300 1ml 2药丸1口3麻醉2009-10-25 10： 10：06.077 1 2009-11-15 07：06：03.733 1 2009-10-25 10：10：06.077 83222B76-6690-42F2-BDAD-BE5BD2D19D3B通用药物8ED135ED-5FDB-419A-9C7D-E788A40EAEAC药物分类二61D618F3-A38A-4416-B0A4-FB29AD614B4B药物分类一个2 400 1ml 2药丸1口3麻醉2009-10-25 10：10：06.077 1 2009-11-15 07：06：03.733 1 2009- 10-25 10：10：06.077 83222B76-6690-42F2-BDAD-BE5BD2D19D3B通用药物一8ED135ED-5FDB-419A-9C7D-E788A40EAEAC药物亚分类二61D618F3-A38A-4416-B0A4-FB29AD614B4B药物分类一个1 ml 2 Pill Pill 2注射3麻醉2009-10-25 10：10：06.077 1 2009-11-15 07：06：03.733 1 2009-10-25 10：10：06.077 A1D86FD2-2E65-4F2A -B7A2-7B083B71AB9B通用药物二9925D762-34B8-43B4-A96B-78374F0081B0药物分类一个C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F药物分类两个1 200 1ml 2药丸1口2无2009-10-25 10:47 ：10.623 1 2009-11-15 04：02：47.890 1 2009-10-25 10：47：10.623 这是我现在得到的： GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName StrengthID GenericDrugDetailsID NumberOfCombinations DosageStrength1 PresentationUnitID1 PresentationUnitName1 DosageFormID1 DosageFormName1 DosageFormNameAbbrev1 RouteOfAdministrationID1 RouteOfAdministrationName1 StrengthID2 DosageStrength2 PresentationUnitID2 PresentationUnitName2 DosageFormID2 DosageFormName2 DosageFormNameAbbrev2 RouteOfAdministrationID2 RouteOfAdministrationName2 InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId版本 ------------------------------- ----- --------------------------------------------- -------------------------------------------------- ----- ------------------------------------ --------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------- --------- --------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------- ----------- ------------ -------- -------------------- --------------- ------- ------------ -------------------------------------- -------------------------------------------------- ------------ ------------- ------------------------- -------------------------------------------------- ------------------------- ------------------------- ------------------------- ------------------------ - -------------------------------------------------- ------------------------------------------------- - ---------- --------------- ------------------- ------ -------------------------------------------------- -------------------------------------------- ------ ------- ------------------------------------------- -------------------------------------------------- ------- ------------------------------------------- ------- ------------------------ ------------------- -------------------------------------------------- ------------------------------- ------------------- ---- --------------------- ----------------------- - ------------------- ----------------------- 83222B76-6690- 42F2-BDAD-BE5BD2D19D3B通用药物8ED135ED-5FDB-419A-9C7D-E788A40EAEAC药物亚分类2 61D618F3-A38A-4416-B0A4-FB29AD614B4B药物分类1 2 2 2 300 1ml 2丸剂1口腔3 400 1ml 2丸剂1 Mouth 2009-10-25 10：10：06.077 1 2009-11-15 07：06：03.733 1 2009-10-25 10：10：06.077 83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC药物分类两个61D618F3-A38A-4416-B0A4-FB29AD614B4B药物分类一个4 4 1 500 1 ml 2药丸2注射NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10 ：10：06.077 1 2009-11-15 07：06：03.733 1 2009-10-25 10：10：06.077 A1D86FD2-2E65-4F2A-B7A2-7B083B71AB9B通用药物二9925D762-34B8-43B4-A96B- 78374F0081B0药物分类一个C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F药物分类二1 1 1 200 1 ml 2药丸1口NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10：47：10.623 1 2009-11 -15 04：02：47.890 1 2009-10-25 10：47：10.623 这些是表结构： CREATE TABLE [dbo]。[Pharmacy_GenericDrug]（ [GenericDrugID] [uniqueidentifier]非NULL CONSTRAINT [DF__Pharmacy___Gener__4E53A1AA] DEFAULT（newid（））， [GenericDrugName] [varchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL， [DrugSubClassificationID] [uniqueidentifier] NOT NULL， [ControlStatusID ] [int] NOT NULL， [InsertDecate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_InsertDate] DEFAULT（getdate（））， [InsertFKUserAccountId] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_Version] DEFAULT（getdate（））$ [$ [$] ）） CREATE TABLE [dbo]。[Pharmacy_GenericDrugDetails]（ [GenericDrugDetailsID] [int] IDENTITY（1,1）NOT NULL， [GenericDrugID] [uniqueidentifier] NOT NULL， [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_InsertDate] DEFAULT（getdate（））， [InsertFKUserAccountId] [int] NOT NULL， [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_Version] DEFAULT（getDate（））， [UpdateFKUserAccountId] [int] getdate()) ) CREATE TABLE [dbo].[Pharmacy_Strengths]( [StrengthID] [int] IDENTITY(1,1) NOT NULL, [GenericDrugDetailsID] [int] NOT NULL, [DosageStrength] [int] NOT NULL, [PresentationUnitID] [int] NOT NULL, [DosageFormID] [int] NOT NULL, [RouteOfAdministrationID] [int] NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL , [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_Version] DEFAULT (getdate()) ) CREATE TABLE [dbo].[Pharmacy_PresentationUnit]( [PresentationUnitID] [int] IDENTITY(1,1) NOT NULL, [PresentationUnitName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, $ b$b [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_Version] DEFAULT (getdate()) ) CREATE TABLE [dbo].[Pharmacy_DosageForm ]( [DosageFormID] [int] IDENTITY(1,1) NOT NULL, [DosageFormName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DosageFormNameAbbrev] [varchar]( 50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_Version] DEFAULT (getdate() ) ) CREATE TABLE [dbo].[Pharmacy_RouteOfAdministration]( [RouteOfAdministrationID] [int] IDENTITY(1,1) NOT NULL, $b$ b [RouteOfAdministrationName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL , [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_Version] DEFAULT (getdate()) ) I have three tables which are:A(a1, a2, a3)//This tbl (Table) can have multiple instances of a1, but cause of its dependence on b1,//we have a unique record each timeB(b1, a1, b2)//tbl C has a relationship with tbl B through b1. b1 can also have multiple instances, but//sometimes a number of unique records in this table can tie to just one record in the B tbl.C(c1, b1, c2, c3)Example://Table B sample datab1 a1 b21 25 paper2 29 pencil3 29 parker//Table C sample datac1 b1 c2 c31 1 w long2 2 b long3 2 g short4 3 v fatExplanation:For the record 2 in tbl B, records 2 and 3 in tbl C should form a single record for it.From every thing so far, tbl A can have multiple records in tbl B which are distinct to tbl A. Tbl B can also have multiple records in tbl C, but those multiple records must be merged into a single record (Where ever there is a duplicate foreign key in tbl C, then a merging should occur).I hope i have done a good job at explaining my problem. I need a query to do this. Can anyone help please?Addition #1:In an effort to make it clearer i'll use the really situation im faced with.Every drug is grouped under a main classification, sub classification, and a generic name.-Main Classification -Sub Classification -Generic NameEach generic name can have more than one strength:Example: -TRIMETHOPRIM Strengths: 100mg, 200mgThere are also times where you can have a generic name that is a combination of two drugs and these two drugs have their strengths, which counts as on strength for this generic name. You can also have multiple.Example: -SULFAMETHOXAZOLE & TRIMETHOPRIM Strengths: 40mg & 8mg/mL, 400mg & 80mg, 800mg & 160mgSo, to enable me keep track of the individual strengths of the combined generic name, i needed another table.GenericTbl(Id, Name, ...)GenericDetails(Id, GenericId, ...)Strenghts(Id, GenericDetailsId, Strength, Unit, DosageForm, ...)I hope this helps.Addition #2I have changed the ids to integers instead of guid (uniqueidentifier)Pharmacy_GenericDrugGenericDrugID GenericDrugName DrugSubClassificationID ControlStatusID1 TRIMETHOPRIM 12 22 SULFAME & TRIMETHOPRIM 4 1Pharmacy_GenericDrugDetailsGenericDrugDetailsID GenericDrugID1 12 2Pharmacy_StrengthsStrengthID GenericDrugDetailsID DosageStrength Unit1 1 200 mg2 2 80 mg3 2 8 mg/LFor the first#1 GenericDrugID = 1: TRIMETHOPRIM | 12 | 200 | mg | 1For the second#2 GenericDrugID = 2: SULFAME & TRIMETHOPRIM | 4 | 80 | mg | 8 | mg/L | 11 > GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | ControlStatusID2 > GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | DosageSrength | Unit | ControlStatusID 解决方案 This is the query that merges two rows based on a foreign key (breaks the rule of 1NF).DECLARE @StrengthID INTDECLARE @GenericDrugDetailsID INTDEClARE @DosageStrength INTDECLARE @PresentationUnitID INTDECLARE @DosageFormID INTDECLARE @RouteOfAdministrationID INTDECLARE @nCombinations INTDECLARE @maxCombinations INTDECLARE @CurrCombinationTotal INTDECLARE @CurrGenericDrugDetailsID INTDECLARE @PName VARCHAR(100)DECLARE @DName VARCHAR(100)DECLARE @DNameAbbrev VARCHAR(50)DECLARE @RName VARCHAR(100)DECLARE @GetGenericDrugStrengths CURSORSET @nCombinations = 0SET @CurrGenericDrugDetailsID = 0--Get the maximum number of combinations--=====================================================================================SET @maxCombinations = (SELECT TOP 1 COUNT(GenericDrugDetailsID) AS maxCombinationsFROM Pharmacy_StrengthsGROUP BY GenericDrugDetailsIDORDER BY maxCombinations desc)--=====================================================================================--Create a temporary temp to hold the strengths--=====================================================================================DECLARE @tmpSQL VARCHAR(max)SET @tmpSQL = ''IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE NAME LIKE '%tmpStrengths%')DROP TABLE #tmpStrengthsCREATE TABLE #tmpStrengths ( StrengthID INT NOT NULL, GenericDrugDetailsID INT NOT NULL, NumberOfCombinations INT NOT NULL, DosageStrength1 INT NOT NULL, PresentationUnitID1 INT NOT NULL, PresentationUnitName1 VARCHAR(100) NOT NULL, DosageFormID1 INT NOT NULL, DosageFormName1 VARCHAR(100) NOT NULL, DosageFormNameAbbrev1 VARCHAR(50) NULL, RouteOfAdministrationID1 INT NOT NULL, RouteOfAdministrationName1 VARCHAR(100) NOT NULL); IF (@maxCombinations > 1) BEGIN DECLARE @counter int SET @counter = 1 WHILE @counter < @maxCombinations BEGIN SET @counter = @counter + 1 SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD StrengthID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL;' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageStrength' + CAST(@counter AS VARCHAR(50)) + ' INT NULL;' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD PresentationUnitID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD PresentationUnitName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormNameAbbrev' + CAST(@counter AS VARCHAR(50)) + ' VARCHAR(50) NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; ' SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; ' END ENDEXEC(@tmpSQL)--====================================================================================SET @tmpSQL = ''SET @GetGenericDrugStrengths = CURSOR FOR SELECT StrengthID, GenericDrugDetailsID, DosageStrength, PresentationUnitID, DosageFormID, RouteOfAdministrationID FROM Pharmacy_Strengths ORDER BY GenericDrugDetailsID ASCOPEN @GetGenericDrugStrengths FETCH NEXT FROM @GetGenericDrugStrengths INTO @StrengthID, @GenericDrugDetailsID, @DosageStrength, @PresentationUnitID, @DosageFormID, @RouteOfAdministrationID WHILE @@FETCH_STATUS = 0 BEGIN --Get the values of Presentation Unit, Dosage Form, and Route of Administration. SELECT @PName = PresentationUnitName FROM Pharmacy_PresentationUnit WHERE PresentationUnitID = @PresentationUnitID SELECT @DName = DosageFormName, @DNameAbbrev = DosageFormNameAbbrev FROM Pharmacy_DosageForm WHERE DosageFormID = @DosageFormID SELECT @RName = RouteOfAdministrationName FROM Pharmacy_RouteOfAdministration WHERE RouteOfAdministrationID = @RouteOfAdministrationID IF (@GenericDrugDetailsID = @CurrGenericDrugDetailsID) BEGIN SET @nCombinations = (@nCombinations + 1) --Alter the temporary table now SET @tmpSQL = @tmpSQL + 'UPDATE #tmpStrengths SET StrengthID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@StrengthID AS VARCHAR(50)) + ', DosageStrength' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@DosageStrength AS VARCHAR(50)) + ', PresentationUnitID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@PresentationUnitID AS VARCHAR(50)) + ', PresentationUnitName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@PName AS VARCHAR(100)) + ''', DosageFormID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@DosageFormID AS VARCHAR(50)) + ', DosageFormName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@DName AS VARCHAR(100)) + ''', DosageFormNameAbbrev' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@DNameAbbrev AS VARCHAR(50)) + ''', RouteOfAdministrationID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@RouteOfAdministrationID AS VARCHAR(50)) + ', RouteOfAdministrationName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@RName AS VARCHAR(100)) + ''' WHERE GenericDrugDetailsID = ' + CAST(@GenericDrugDetailsID AS VARCHAR(50)); EXEC(@tmpSQL); END ELSE BEGIN SET @nCombinations = 1 SET @CurrGenericDrugDetailsID = @GenericDrugDetailsID; --Get the total number of combinations in advance SET @CurrCombinationTotal = (SELECT COUNT(GenericDrugDetailsID) FROM Pharmacy_Strengths WHERE GenericDrugDetailsID = @GenericDrugDetailsID GROUP BY GenericDrugDetailsID); --Insert in the temporary table now INSERT INTO #tmpStrengths (StrengthID, GenericDrugDetailsID, NumberOfCombinations, DosageStrength1, PresentationUnitID1, PresentationUnitName1, DosageFormID1, DosageFormName1, DosageFormNameAbbrev1, RouteOfAdministrationID1, RouteOfAdministrationName1) VALUES (@StrengthID, @GenericDrugDetailsID, @CurrCombinationTotal, @DosageStrength, @PresentationUnitID, @PName, @DosageFormID, @DName, @DNameAbbrev, @RouteOfAdministrationID, @RName) END --PRINT CAST(@StrengthID AS varchar(50)) + ' ' + CAST(@GenericDrugDetailsID AS varchar(50)) + ' ' + CAST(@DosageStrength AS varchar(50)) + ' ' + CAST(@PresentationUnitID AS varchar(50)) + ' ' + CAST(@DosageFormID as varchar(50)) + ' ' + CAST(@RouteOfAdministrationID as varchar(50)) FETCH NEXT FROM @GetGenericDrugStrengths INTO @StrengthID, @GenericDrugDetailsID, @DosageStrength, @PresentationUnitID, @DosageFormID, @RouteOfAdministrationID END --SELECT * from #tmpStrengthsCLOSE @GetGenericDrugStrengthsDEALLOCATE @GetGenericDrugStrengthsSELECT a.GenericDrugID, a.GenericDrugName, f.*, a.InsertDate, a.InsertFKUserAccountId, a.UpdateDate, a.UpdateFKUserAccountId, a.VersionFROM Pharmacy_GenericDrug aINNER JOIN Pharmacy_ControlStatus d ON d.ControlStatusID = a.ControlStatusIDINNER JOIN Pharmacy_GenericDrugDetails e ON e.GenericDrugID = a.GenericDrugIDINNER JOIN #tmpStrengths f ON f.GenericDrugDetailsID = e.GenericDrugDetailsIDORDER BY GenericDrugName ASCThis is what i get before the query:GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName GenericDrugDetailsID DosageStrength PresentationUnitID PresentationUnitName DosageFormID DosageFormName DosageFormNameAbbrev RouteOfAdministrationID RouteOfAdministrationName ControlStatusID ControlStatusName InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId Version------------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------- ------------------ ---------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------------------- ---------------------------------------------------------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------- --------------------- ----------------------- --------------------- -----------------------83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 300 1 ml 2 Pill Pill 1 Mouth 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.07783222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 400 1 ml 2 Pill Pill 1 Mouth 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.07783222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 4 500 1 ml 2 Pill Pill 2 Injection 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077A1D86FD2-2E65-4F2A-B7A2-7B083B71AB9B Generic Drug Two 9925D762-34B8-43B4-A96B-78374F0081B0 Drug Subclassification One C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F Drug Classification Two 1 200 1 ml 2 Pill Pill 1 Mouth 2 None 2009-10-25 10:47:10.623 1 2009-11-15 04:02:47.890 1 2009-10-25 10:47:10.623This is what i get now:GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName StrengthID GenericDrugDetailsID NumberOfCombinations DosageStrength1 PresentationUnitID1 PresentationUnitName1 DosageFormID1 DosageFormName1 DosageFormNameAbbrev1 RouteOfAdministrationID1 RouteOfAdministrationName1 StrengthID2 DosageStrength2 PresentationUnitID2 PresentationUnitName2 DosageFormID2 DosageFormName2 DosageFormNameAbbrev2 RouteOfAdministrationID2 RouteOfAdministrationName2 InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId Version------------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- --------------- ------------------- ---------------------------------------------------------------------------------------------------- ------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------- ----------- --------------- ------------------- ---------------------------------------------------------------------------------------------------- ------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------- ----------------------- --------------------- ----------------------- --------------------- -----------------------83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 2 2 300 1 ml 2 Pill Pill 1 Mouth 3 400 1 ml 2 Pill Pill 1 Mouth 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.07783222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 4 4 1 500 1 ml 2 Pill Pill 2 Injection NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077A1D86FD2-2E65-4F2A-B7A2-7B083B71AB9B Generic Drug Two 9925D762-34B8-43B4-A96B-78374F0081B0 Drug Subclassification One C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F Drug Classification Two 1 1 1 200 1 ml 2 Pill Pill 1 Mouth NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10:47:10.623 1 2009-11-15 04:02:47.890 1 2009-10-25 10:47:10.623For those interested these are the table structures: CREATE TABLE [dbo].[Pharmacy_GenericDrug]( [GenericDrugID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Pharmacy___Gener__4E53A1AA] DEFAULT (newid()), [GenericDrugName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DrugSubClassificationID] [uniqueidentifier] NOT NULL, [ControlStatusID] [int] NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_Version] DEFAULT (getdate()) )CREATE TABLE [dbo].[Pharmacy_GenericDrugDetails]( [GenericDrugDetailsID] [int] IDENTITY(1,1) NOT NULL, [GenericDrugID] [uniqueidentifier] NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_Version] DEFAULT (getdate()))CREATE TABLE [dbo].[Pharmacy_Strengths]( [StrengthID] [int] IDENTITY(1,1) NOT NULL, [GenericDrugDetailsID] [int] NOT NULL, [DosageStrength] [int] NOT NULL, [PresentationUnitID] [int] NOT NULL, [DosageFormID] [int] NOT NULL, [RouteOfAdministrationID] [int] NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_Version] DEFAULT (getdate()))CREATE TABLE [dbo].[Pharmacy_PresentationUnit]( [PresentationUnitID] [int] IDENTITY(1,1) NOT NULL, [PresentationUnitName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_Version] DEFAULT (getdate()))CREATE TABLE [dbo].[Pharmacy_DosageForm]( [DosageFormID] [int] IDENTITY(1,1) NOT NULL, [DosageFormName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DosageFormNameAbbrev] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_Version] DEFAULT (getdate()))CREATE TABLE [dbo].[Pharmacy_RouteOfAdministration]( [RouteOfAdministrationID] [int] IDENTITY(1,1) NOT NULL, [RouteOfAdministrationName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_InsertDate] DEFAULT (getdate()), [InsertFKUserAccountId] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_UpdateDate] DEFAULT (getdate()), [UpdateFKUserAccountId] [int] NOT NULL, [Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_Version] DEFAULT (getdate())) 这篇关于如何基于其外键合并两个或更多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！
09-09 14:01
Powered by LMLPHP ©2024 如何基于其外键合并两个或更多行 0.040525