sql - How do i merge two or more rows based on their foreign key?

Question

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 time
B(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 data
b1     a1     b2
1      25     paper
2      29     pencil
3      29     parker

//Table C sample data
c1     b1     c2     c3
1      1      w      long
2      2      b      long
3      2      g      short
4      3      v      fat

Explanation: 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 Name

Each generic name can have more than one strength: Example:

 -TRIMETHOPRIM
  Strengths: 100mg, 200mg

There 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 & 160mg

So, 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 #2 I have changed the ids to integers instead of 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       DosageStrength        Unit
1                  1                          200                   mg
2                  2                          80                    mg
3                  2                          8                     mg/L


For the first#1 GenericDrugID = 1: TRIMETHOPRIM | 12 | 200 | mg | 1

For the second#2 GenericDrugID = 2: SULFAME & TRIMETHOPRIM | 4 | 80 | mg | 8 | mg/L | 1

1 > GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | ControlStatusID

2 > GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | DosageSrength | Unit | ControlStatusID

Answer 1

This is the query that merges two rows based on a foreign key (breaks the rule of 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

--Get the maximum number of combinations
--=====================================================================================
SET @maxCombinations = (SELECT TOP 1 COUNT(GenericDrugDetailsID) AS maxCombinations
FROM Pharmacy_Strengths
GROUP BY GenericDrugDetailsID
ORDER 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 #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(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
             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

        --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 #tmpStrengths
CLOSE @GetGenericDrugStrengths
DEALLOCATE @GetGenericDrugStrengths

SELECT a.GenericDrugID, 
       a.GenericDrugName,
       f.*,
       a.InsertDate, 
       a.InsertFKUserAccountId, 
       a.UpdateDate, 
       a.UpdateFKUserAccountId, 
       a.Version
FROM 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

This 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