Как создать вложенные таблицы на сайте

В этом примере мы реализовали подтаблицы (вложенные таблицы).

Для этого мы:

• создали таблицу
• добавили необходимые колонки (id, name, fio, countOrders, sumOrders)
• настроили GetItems таблицы, где для отображения подтаблицы добавили колонку sub_{имя колонки} со сниппетом разметки подтаблицы (в колонки таблицы это поле добавлять не нужно)
• в таблице, используемой как подтаблица (в нашем случае watch_subTables), настраиваем GetItems
• в последней подтаблице (watch_createSearch) также настраиваем GetItems

CREATE PROCEDURE [dbo].[crud_watch_table_getItems]
	@filters CRUDFilterParameter READONLY,  
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare   @ids TABLE (id int)

	-- filters...
	declare @filterItemID nvarchar(128)
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'
	
	insert into @ids
	select id
	from tst_customers
	where (isnull(@filterItemID, 0)= 0 or id = @filterItemID)
	
	-- SELECT 1
	select 
		id id,
		isnull(name, '') name,       
		isnull(fio, '') fio,
        (select count(*) from tst_orders where customerID = c.id) countOrders,
    'тут разметка сниппета подтаблицы...'   sub_countOrders,
        isnull((select sum(price) from tst_orders where customerID = c.id), 0) sumOrders
	from tst_customers c
	where id in (select id from @ids)
	order by  
		case when @sort = ''  then id end asc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'fio' and @direction = 'up' then fio end asc,
        case when @sort = 'fio' and @direction = 'down' then fio end desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- SELECT 2
	select count(*) from @ids	

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount
END

--ru 30.05.2021 10:59:26

Код для вложенной таблицы

CREATE PROCEDURE [dbo].[crud_watch_subTables_getItems]
	@filters CRUDFilterParameter READONLY,  
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare   @ids TABLE (id int)

	-- filters...
	declare @filterItemID int
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'
	
	insert into @ids
	select id
	from tst_orders
    where isnull(@filterItemID, 0) = 0 or customerID = @filterItemID
	
	-- SELECT 1
	select 
		id id,
		'' product,
        'тут сниппет для влож таблицы' sub_product,
		FORMAT(created, 'dd.MM.yyyy') created,
		(select name from tst_statuses where id = o.id) status,
		isnull(price, 0) price
	from tst_orders o
	where id in (select id from @ids)
	order by  
    	case when @sort = '' then created end desc,
		case when @sort = 'created' and @direction = 'down' then created end desc,
		case when @sort = 'created' and @direction = 'up' then created end asc,
        case when @sort = 'price' and @direction = 'down' then price end desc,
		case when @sort = 'price' and @direction = 'up' then price end asc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- SELECT 2
	select count(*) from @ids	

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount
END

Код для вложенной таблицы 2 уровня. 

CREATE PROCEDURE [dbo].[crud_watch_createSearch_getItems]
	@filters CRUDFilterParameter READONLY,
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare   @ids TABLE (id int)

	-- filters...
	declare @filterItemID nvarchar(128)
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'

	insert into @ids
	select id
	from tst_products
	where (isnull(@filterItemID, 0) = 0 or id = @filterItemID)

	-- SELECT 1
	select
		id id,
		isnull(name, '') name,
        isnull(art, 'Не указано') art,
		isnull([desc], 'Не указано') [desc],
        isnull(try_convert(datetime, created, 104), '')created,
        isnull(isVisible, 0) isVisible,
        isnull((select name from tst_categories where id = (select categoryID from tst_categoryProducts where productID = p.id)), 'Не указано') category
	from tst_products p
	where id in (select id from @ids)
	order by
		case when @sort = ''  then id end asc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'price' and @direction = 'up' then price end asc,
        case when @sort = 'price' and @direction = 'down' then price end desc,
        case when @sort = 'desc' and @direction = 'up' then [desc] end asc,
        case when @sort = 'desc' and @direction = 'down' then [desc] end desc,
        case when @sort = 'created' and @direction = 'up' then created end asc,
        case when @sort = 'created' and @direction = 'down' then created end desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

	-- SELECT 2
	select count(*) from @ids

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount, iif(@filterItemID is null, 1, 0) FastCreate, 1 FastCreateSearch
END