clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130703 fiddles created (16576 in the last week).

-- tables
 hidden batch(es)


CREATE TABLE [dbo].[Cells]( [ID] [int] IDENTITY(1,1) NOT NULL, [ColumnID] [int] NOT NULL, [RowID] [int] NOT NULL, [RowValueID] [int] NULL, [NativeValue] [nvarchar](max) NULL, CONSTRAINT [PK_Cells] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 hidden batch(es)


CREATE TABLE [dbo].[Columns]( [ID] [int] IDENTITY(1,1) NOT NULL, [SheetID] [int] NOT NULL, [NativeTypeID] [int] NULL, [SheetTypeID] [int] NULL, [Name] [nvarchar](50) NOT NULL, [ColumnSameSheetInheritFromID] [int] NULL, [ColumnRelatedToFromColumnTypeID] [int] NULL, CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
 hidden batch(es)


CREATE TABLE [dbo].[Native_Types]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Native_Types] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
 hidden batch(es)


CREATE TABLE [dbo].[Rows]( [ID] [int] IDENTITY(1,1) NOT NULL, [SheetID] [int] NOT NULL, [Identificator] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Rows] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
 hidden batch(es)


CREATE TABLE [dbo].[Sheets]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NULL, [WhenIsTypeShowThisColumnIDValue] [int] NOT NULL, CONSTRAINT [PK_Sheet] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
 hidden batch(es)


-- data load
 hidden batch(es)


SET IDENTITY_INSERT [dbo].[Cells] ON INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (1, 1, 1, NULL, N'Aron') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (2, 2, 1, 3, NULL) INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (4, 1, 2, NULL, N'Sam') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (8, 4, 3, NULL, N'Uruguay') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (9, 4, 4, NULL, N'USA') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (10, 4, 5, NULL, N'Russia') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (11, 5, 6, NULL, N'Mickey') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (12, 6, 6, NULL, N'83') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (13, 5, 7, NULL, N'Pluto') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (14, 6, 7, NULL, N'8') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (15, 5, 8, NULL, N'Zabivaka') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (16, 6, 8, NULL, N'4') INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (17, 7, 6, 1, NULL) INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (18, 7, 7, 1, NULL) INSERT [dbo].[Cells] ([ID], [ColumnID], [RowID], [RowValueID], [NativeValue]) VALUES (19, 7, 8, 2, NULL) SET IDENTITY_INSERT [dbo].[Cells] OFF SET IDENTITY_INSERT [dbo].[Columns] ON INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (1, 1, 1, NULL, N'Person Name', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (2, 1, NULL, 2, N'Person Country', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (4, 2, 1, NULL, N'Country Name', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (5, 3, 1, NULL, N'Pet Name', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (6, 3, 2, NULL, N'Pet Age', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (7, 3, NULL, 1, N'Pet Person Owner', NULL, NULL) INSERT [dbo].[Columns] ([ID], [SheetID], [NativeTypeID], [SheetTypeID], [Name], [ColumnSameSheetInheritFromID], [ColumnRelatedToFromColumnTypeID]) VALUES (8, 3, NULL, NULL, N'Pet Country', 7, 2) SET IDENTITY_INSERT [dbo].[Columns] OFF SET IDENTITY_INSERT [dbo].[Native_Types] ON INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (1, N'Text') INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (2, N'Number') INSERT [dbo].[Native_Types] ([ID], [Name]) VALUES (3, N'Boolean') SET IDENTITY_INSERT [dbo].[Native_Types] OFF SET IDENTITY_INSERT [dbo].[Rows] ON INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (1, 1, N'Person A') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (2, 1, N'Person B') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (3, 2, N'Country A') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (4, 2, N'Country B') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (5, 2, N'Country C') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (6, 3, N'Pet X') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (7, 3, N'Pet Y') INSERT [dbo].[Rows] ([ID], [SheetID], [Identificator]) VALUES (8, 3, N'Pet Z') SET IDENTITY_INSERT [dbo].[Rows] OFF SET IDENTITY_INSERT [dbo].[Sheets] ON INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (1, N'Persons', 1) INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (2, N'Countries', 4) INSERT [dbo].[Sheets] ([ID], [Name], [WhenIsTypeShowThisColumnIDValue]) VALUES (3, N'Pets', 5) SET IDENTITY_INSERT [dbo].[Sheets] OFF
36 rows affected
 hidden batch(es)


-- constraints
 hidden batch(es)


ALTER TABLE [dbo].[Cells] WITH CHECK ADD CONSTRAINT [FK_(Cells)_(Columns)_BelongsTo] FOREIGN KEY([ColumnID]) REFERENCES [dbo].[Columns] ([ID]) ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Columns)_BelongsTo] ALTER TABLE [dbo].[Cells] WITH CHECK ADD CONSTRAINT [FK_(Cells)_(Rows)_BelongsTo] FOREIGN KEY([RowID]) REFERENCES [dbo].[Rows] ([ID]) ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Rows)_BelongsTo] ALTER TABLE [dbo].[Cells] WITH CHECK ADD CONSTRAINT [FK_(Cells)_(Rows)_Value] FOREIGN KEY([RowValueID]) REFERENCES [dbo].[Rows] ([ID]) ALTER TABLE [dbo].[Cells] CHECK CONSTRAINT [FK_(Cells)_(Rows)_Value] ALTER TABLE [dbo].[Columns] WITH CHECK ADD CONSTRAINT [FK_(Columns)_(Columns)_ReferenceFromColumn] FOREIGN KEY([ColumnSameSheetInheritFromID]) REFERENCES [dbo].[Columns] ([ID]) ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Columns)_ReferenceFromColumn] ALTER TABLE [dbo].[Columns] WITH CHECK ADD CONSTRAINT [FK_(Columns)_(Columns)_ReferenceToColumn] FOREIGN KEY([ColumnRelatedToFromColumnTypeID]) REFERENCES [dbo].[Columns] ([ID]) ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Columns)_ReferenceToColumn] ALTER TABLE [dbo].[Columns] WITH CHECK ADD CONSTRAINT [FK_(Columns)_(Native_Types)_Type] FOREIGN KEY([NativeTypeID]) REFERENCES [dbo].[Native_Types] ([ID]) ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Native_Types)_Type] ALTER TABLE [dbo].[Columns] WITH CHECK ADD CONSTRAINT [FK_(Columns)_(Sheets)_BelongsTo] FOREIGN KEY([SheetID]) REFERENCES [dbo].[Sheets] ([ID]) ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Sheets)_BelongsTo] ALTER TABLE [dbo].[Columns] WITH CHECK ADD CONSTRAINT [FK_(Columns)_(Sheets)_Type] FOREIGN KEY([SheetTypeID]) REFERENCES [dbo].[Sheets] ([ID]) ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_(Columns)_(Sheets)_Type] ALTER TABLE [dbo].[Rows] WITH CHECK ADD CONSTRAINT [FK_(Rows)_(Sheets)_BelongsTo] FOREIGN KEY([SheetID]) REFERENCES [dbo].[Sheets] ([ID]) ALTER TABLE [dbo].[Rows] CHECK CONSTRAINT [FK_(Rows)_(Sheets)_BelongsTo] ALTER TABLE [dbo].[Sheets] WITH CHECK ADD CONSTRAINT [FK_(Sheets)_(Columns)_ColumnToShowValue] FOREIGN KEY([WhenIsTypeShowThisColumnIDValue]) REFERENCES [dbo].[Columns] ([ID]) ALTER TABLE [dbo].[Sheets] CHECK CONSTRAINT [FK_(Sheets)_(Columns)_ColumnToShowValue]
 hidden batch(es)


-- functions
 hidden batch(es)


CREATE FUNCTION [dbo].[GetInheritedCells](@rowID INT, @colID INT) RETURNS @result TABLE (ID INT) AS BEGIN DECLARE @ColumnSameSheetInheritFromID INT = NULL; DECLARE @ColumnRelatedToFromColumnTypeID INT = NULL; --GETTING COLUMN FROM AND COLUMN VALUE IDs SELECT @ColumnSameSheetInheritFromID = col.ColumnSameSheetInheritFromID, @ColumnRelatedToFromColumnTypeID = col.ColumnRelatedToFromColumnTypeID FROM dbo.[Columns] col WHERE col.ID = @colID; --IF COLUMN IS NOT INHERITED FUNCTION ENDS IF @ColumnSameSheetInheritFromID IS NULL OR @ColumnRelatedToFromColumnTypeID IS NULL BEGIN RETURN; END DECLARE @cellFromID INT = NULL; DECLARE @cellInhValueID INT = NULL; DECLARE @colFromAUX_ID INT = NULL; DECLARE @cellHaveValue INT = NULL; --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW SELECT @cellFromID = cls.ID, @colFromAUX_ID = col.ColumnSameSheetInheritFromID, @cellHaveValue = IIF(cls.RowValueID IS NULL, 0, 1) FROM dbo.[Rows] r LEFT OUTER JOIN dbo.[Columns] col ON r.SheetID = col.SheetID LEFT OUTER JOIN dbo.[Cells] cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID WHERE r.ID = @rowID AND col.ID = @ColumnSameSheetInheritFromID IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL SET @cellFromID = (SELECT TOP 1 ID FROM dbo.GetInheritedCells(@rowID, @ColumnSameSheetInheritFromID)); ELSE IF @cellFromID IS NULL AND @ColumnSameSheetInheritFromID IS NULL SET @cellFromID = -1; IF @cellFromID > 0 BEGIN SELECT @rowID = cls.RowValueID FROM dbo.[Cells] cls INNER JOIN dbo.[Columns] col ON cls.ColumnID = col.ID WHERE cls.ID = @cellFromID; IF @rowID IS NOT NULL BEGIN --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW SELECT @cellInhValueID = cls.ID, @colFromAUX_ID = col.ColumnSameSheetInheritFromID, @cellHaveValue = (IIF(cls.RowValueID IS NULL, IIF(cls.NativeValue IS NULL, 0, 1),1)) FROM dbo.[Rows] r LEFT OUTER JOIN dbo.[Columns] col ON r.SheetID = col.SheetID LEFT OUTER JOIN dbo.[Cells] cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL BEGIN INSERT INTO @result SELECT ID FROM dbo.GetInheritedCells(@rowID, @ColumnRelatedToFromColumnTypeID); END ELSE BEGIN INSERT INTO @result SELECT cls.ID FROM dbo.[Rows] r LEFT OUTER JOIN dbo.[Columns] col ON r.SheetID = col.SheetID LEFT OUTER JOIN dbo.[Cells] cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID; END END END RETURN; END
 hidden batch(es)


-- views
 hidden batch(es)


CREATE VIEW [dbo].[RowsData] AS SELECT r.ID, s.ID SheetID, col.ID ColID, /*Columns Added*/col.ColumnSameSheetInheritFromID, col.ColumnRelatedToFromColumnTypeID, col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue FROM Sheets s INNER JOIN [Columns] col ON s.ID = col.SheetID INNER JOIN [Rows] r ON s.ID = r.SheetID /*OUTER APPLY added*/ OUTER APPLY (SELECT ID FROM dbo.GetInheritedCells(r.ID, col.ID)) inhCel LEFT JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID /* This added -> */ OR inhCel.ID = cls.ID LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
 hidden batch(es)


-- queries
 hidden batch(es)


select * from RowsData;
ID SheetID ColID ColumnSameSheetInheritFromID ColumnRelatedToFromColumnTypeID Name NativeValue
1 1 1 Person Name Aron
1 1 2 Person Country Uruguay
2 1 1 Person Name Sam
2 1 2 Person Country
3 2 4 Country Name Uruguay
4 2 4 Country Name USA
5 2 4 Country Name Russia
6 3 5 Pet Name Mickey
6 3 6 Pet Age 83
6 3 7 Pet Person Owner Aron
6 3 8 7 2 Pet Country Uruguay
7 3 5 Pet Name Pluto
7 3 6 Pet Age 8
7 3 7 Pet Person Owner Aron
7 3 8 7 2 Pet Country Uruguay
8 3 5 Pet Name Zabivaka
8 3 6 Pet Age 4
8 3 7 Pet Person Owner Sam
8 3 8 7 2 Pet Country
 hidden batch(es)