USE [DATA_Volkov] GO /****** Object: StoredProcedure [dbo].[pr_get_pos_by_comment] Script Date: 02.03.2019 20:04:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[pr_get_pos_by_comment] @start int, @end int, @comment nvarchar(100), @filter nvarchar(100) = '--none--' AS BEGIN SET NOCOUNT ON; set @end = @start + @end; With RecordEntries as (SELECT ROW_NUMBER() OVER (ORDER BY TT.f3 DESC) as Row, TT.*, 'Наличие:' + ltrim(Str(PR.Q)) + 'шт. 15 дней' as H, L.list, '' serv from ( SELECT max(NP.f1) f1, NP.f3, NP.f6, Sum(NP.Q) AS Q FROM dbo.ShopSaleNakPos NP INNER JOIN dbo.ShopSaleNakList NL ON NP.idNak = NL.id WHERE NP.t > 0 AND NL.Comment = @comment GROUP BY NP.f3, NP.f6 ) TT left join ( Select f3,Q from [DATA_VOLKOV_serv].[dbo].[tbl_Prices] where idSup = 3) PR on TT.f3 = PR.f3 --все заказы+клиенты по артикулу в одну строку из скалярной фу CROSS APPLY ( select dbo.getListForOrdersByComments(@comment,TT.f3 ) as list) L ) Select * FROM RecordEntries WHERE Row between @start and @end end --******************** --сама dbo.getListForOrdersByComments create FUNCTION [dbo].[getListForOrdersByComments](@comment as nvarchar(100),@NR as nvarchar(100)) RETURNS nvarchar(1000) AS BEGIN --разворачивает столб в строку - переписать если перейдем на 2017 declare @results varchar(500) select @results = coalesce(@results + ', ', '') + convert(varchar(12),id) + ' ' + ClientName from (select id,idClient from ShopSaleNakList where Comment = @comment) NL inner join (select idNak from dbo.ShopSaleNakPos where f3 = @NR) NP ON NP.idNak = NL.id left join dbo.ShopClients CL on cl.idClient = NL.idClient RETURN @results