sql - tablas - ver fk de una tabla oracle

¿Cómo fusiono dos o más filas según su clave externa? (3)

Tengo tres tablas que son:

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)

Ejemplo:

//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

Explicación: Para el registro 2 en tbl B, los registros 2 y 3 en tbl C deben formar un registro único para él.

De todas las cosas hasta ahora, tbl A puede tener múltiples registros en tbl B que son distintos a tbl A. Tbl B también puede tener múltiples registros en tbl C, pero esos registros múltiples deben fusionarse en un solo registro (Donde sea que haya un duplicar la clave foránea en tbl C, luego debe producirse una fusión).

Espero haber hecho un buen trabajo al explicar mi problema. Necesito una consulta para hacer esto. ¿Alguien puede ayudar, por favor?

Suma # 1: En un esfuerzo por hacerlo más claro usaré la situación real que enfrento.

Todos los medicamentos se agrupan bajo una clasificación principal, una clasificación secundaria y un nombre genérico.

-Main Classification -Sub Classification -Generic Name

Cada nombre genérico puede tener más de una fortaleza: Ejemplo:

-TRIMETHOPRIM Strengths: 100mg, 200mg

También hay ocasiones en las que puede tener un nombre genérico que es una combinación de dos medicamentos y estos dos medicamentos tienen sus puntos fuertes, que se relacionan con la fortaleza de este nombre genérico. También puedes tener múltiples. Ejemplo:

-SULFAMETHOXAZOLE & TRIMETHOPRIM Strengths: 40mg & 8mg/mL, 400mg & 80mg, 800mg & 160mg

Entonces, para permitirme hacer un seguimiento de las fortalezas individuales del nombre genérico combinado, necesitaba otra tabla.

GenericTbl(Id, Name, ...) GenericDetails(Id, GenericId, ...) Strenghts(Id, GenericDetailsId, Strength, Unit, DosageForm, ...)

Espero que esto ayude.

Suma # 2 He cambiado los ids a enteros en lugar de guid (uniqueidentifier)

Aquí hay otra solución que usa SQLXML:

SELECT STUFF ( ( SELECT '', '' + cast(id AS NVARCHAR(max)) + '': '' + video FROM Video ORDER BY Id FOR XML PATH ('''') ),1,1,'''' ) CREATE TABLE [dbo].[Video]( [ID] [int] NOT NULL, [Video] [varchar](50) NOT NULL, [Views] [int] NOT NULL )

Resultado: 1: Hulk, 2: Jack, 3: El Rey

Actualización: también acabo de encontrar esto en Data Management and Exchange.

Esta es la consulta que fusiona dos filas basadas en una clave externa (rompe la regla de 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

Esto es lo que obtengo antes de la consulta:

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.077 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 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.077 83222B76-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.077 A1D86FD2-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.623

Esto es lo que tengo ahora:

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.077 83222B76-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.077 A1D86FD2-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.623

Para aquellos interesados estas son las estructuras de la mesa:

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()) )

OK, tomemos una puñalada y veamos si esto nos lleva a algún lado.

Tabla A: GenericTbl (Id, Nombre, ...)

Tabla B: GenericDetails (Id, GenericId, ...)

Tabla C: Fortalezas (Id, GenericDetailsId, Strength, Unit, DosageForm, ...)

Si cuando dices fusionar estás buscando un conjunto de resultados que reúna los resultados para las fortalezas de un solo genérico, podrías hacer algo similar a

SELECT g.Id, g.Name, gd.Id, s.Strength, s.Unit, s.OtherFields /*fill in the blanks*/ FROM GenericTbl g INNER JOIN GenericDetails gd on g.Id=gd.GenericId INNER JOIN Strengths s ON gd.Id=s.GenericDetailsId ORDER BY g.Id, gd.Id, s.Id

y eso le daría un solo conjunto de resultados en el que se enumerarían todas las fortalezas diferentes para cada uno de los genéricos, agrupados sus tipos, con cada resultado en una fila.

Si lo que quiere es una sola fila por tipo con columnas para indicarle las opciones disponibles, debería ser factible con los cursores y las tablas temporales, o pivotes, creo que desde la memoria.

Si lo que realmente está buscando es que los registros se combinen al insertar en la base de datos para que solo tenga un único registro subyacente que contenga todos los datos, entonces puede hacerlo a través de activadores, pero no puedo ver por qué Querría hacerlo porque terminaría con un mapeo 1: 1 para B y C y también podría tenerlos como una sola tabla.