如何使用递归查询构造 SQL 数据透视表

Question

>想象一下我有这些表

工单：

[Id], [ServiceId], [StatusId]

服务<--自参考表

[Id], [SName], [SCode], [SDnaCode], [SerivceParentId:Nullable]

地位

[Id], [StName], [StCode]

现在如何获得这样的数据透视表结果：

|StName   |RootService_1|RootService_2|...
+---------+-------------+-------------+----
|Initiated|23           |12           |...
|Closed   |8            |5            |...

请记住，计数数字用于根服务，并且每个根服务都有自己的层次结构

表中[Service]条记录示例

|ID|SName        |SCode|SDnaCode|SerivceParentId|
+--+-------------+-----+--------+---------------+
| 1|RootService_1|RS01 |RS01    |NULL           |
| 2|RS01_Child_1 |C01  |RS01.C01|1              |
| 3|RS01_Child_2 |C02  |RS01.C02|1              |
| 4|RootService_2|RS02 |RS02    |NULL           |
| 5|RS02_Child_1 |C01  |RS02.C01|2              |
| 6|RS02_Child_2 |C02  |RS02.C02|2              |

因此，示例数据透视表中RootService_1下23的数字是Initiated WorkOrders 的计数，它ServiceId1,2 或 3 的组合

请记住，我想将其包装在动态SQL中，以便为数据透视表for clause生成值。

我希望我很好地解释了我的问题，提前谢谢

Answer 1

我找到了一个解决方案，我希望有一个对文本数据依赖较少的解决方案 此解决方案取决于在我的情况下ServiceDnaCode的SDnaCode列，我将其设置为存储过程

CREATE PROCEDURE DashboardGetServicesDataByStatus @lvl int, @parentId  NVARCHAR(MAX)
AS
BEGIN
DECLARE 
@columns NVARCHAR(MAX) = '', 
@sql     NVARCHAR(MAX) = '',
@sqlCommand nvarchar(max)=''; 
-- select the StatusName
set @sqlCommand = 'SELECT 
@columns+=QUOTENAME(
(SELECT value from string_split(ServiceDnaCode,''.'')
order by CURRENT_TIMESTAMP
OFFSET      @lvl rows
fetch next 1 rows only)) + '',''
FROM 
[Service] where ParentServiceId' + 
case
when @lvl>0 then ' IN(@parentId)'
else    ' is null '
end
+ '
ORDER BY 
Id;';
EXECUTE sp_executesql @sqlCommand, N'@lvl int,@parentId NVARCHAR(max),@columns NVARCHAR(max) OUTPUT'
, @lvl = @lvl,@parentId=@parentId, @columns=@columns OUTPUT
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
print @columns
print @lvl
-- construct dynamic SQL
SET @sql ='
with cte_service as (
select * from [Service] where [Service].Id IN ('+CAST(@parentId as varchar(max))+')
union all
select e.* from [Service] e inner join cte_service o on e.ParentServiceId= o.Id
)
SELECT * FROM (
select count(wo.Id) ID,os.StatusName STATENAME ,
(SELECT value from string_split(ServiceDnaCode,''.'')
order by CURRENT_TIMESTAMP
OFFSET      '+CAST(@lvl as varchar(max))+' rows
fetch next 1 rows only) SNAME from WorkOrder wo 
left join RefWorkOrderStatus os on wo.OrderStatusCode=os.Id 
left join [Service] s on wo.MainServiceId=s.Id
where wo.MainServiceId IN (select Id from cte_service)
group by os.StatusName,s.ServiceDnaCode
) t
pivot(
sum(ID)
for SNAME IN ('+ @columns +')
) as pivottable';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
END
Go