SET LANGUAGE Russian; 

create table #LPur (Product_ID int, Name nvarchar(50))

create table #LPro (ID int, Name nvarchar(50))

insert into #LPur (Product_ID, Name) values (1, N'Потребительский-на медицинское лечение'),(1, N'Потребительский-на обучение')
insert into #LPur (Product_ID, Name) values (2, N'Производство продуктов питания'),(2, N'Производство строит материалов')
insert into #LPur (Product_ID, Name) values (3, N'Услуги транспорта и перевозка грузов'),(3, N'Услуги швейного цеха')

insert into #LPro (ID, Name) values (1, N'Потребительский'),(2, N'Производство'), (3, N'Услуги')



;
with cte (ID, Name)
as
(
select ID, Name from #LPro
union all
select Product_ID, Name from #LPur
),
cte2 AS (
select 
	LPro.Name AS name1, 
	LPur.Name AS name2,
	GROUPING(LPur.Name) AS gr,
	ROW_NUMBER() OVER (PARTITION BY GROUPING(LPur.Name), LPro.Name ORDER BY (SELECT NULL)) AS RN
from cte LPur
left outer join #LPro LPro on LPro.ID=LPur.ID
group by grouping sets ((LPro.Name,LPur.Name), (LPro.Name), (LPro.Name))
) 
SELECT 
	case gr WHEN 0 THEN name2
			WHEN 1 THEN 
				CASE RN WHEN 1 THEN N'ЗАГОЛОВОК:' + Name1
						WHEN 2 THEN N'ИТОГО:' + Name1
				END
	END
FROM cte2
ORDER BY 
	name1, 
	case gr WHEN 0 THEN 1
			WHEN 1 THEN 
				CASE RN WHEN 1 THEN 0
						WHEN 2 THEN 2
				END
	END, 
	name2

drop table #LPro

drop table #LPur