在SQLServer中聚合動態列

我有一個包含唯一鍵和源資料的窄表

我希望能夠選擇一個系統作為基礎——在這種情況下是 IT——然后在它重要的地方創建一個動態 SQL 查詢：

• 所選系統中不同的唯一鍵
• 與其他系統共享唯一密鑰的比例。這些系統可能是動態的，并且有超過 4 個

我正在考慮使用動態 SQL 和 PIVOT 首先挑選出 IT 之外的所有系統名稱。然后以 IT 為基礎，加入該表以獲取資訊。

select distinct Unique_Key, System_Name
into #staging
from dbo.data
where System_Name <> 'IT'

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ','   QUOTENAME(System_Name) 
            FROM #staging
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Unique_Key, '   @cols   ' into dbo.temp from 
            (
                select Unique_Key, System_Name
                from #staging
           ) x
            pivot 
            (
                 count(System_Name)
                for System_Name in ('   @cols   ')
            ) p '

execute(@query)

  select *
  from 
  (
    select distinct Unique_Key
  from dbo.data
  where System_Name = 'IT'
  ) a
  left join dbo.temp b
  on a.Unique_Key = b.Unique_Key

所以結果表是：

我想要的是更進一步：

我可以使用特定的 case when/sum 陳述句進行簡單的連接，但想知道是否有辦法動態地進行連接，因此我不需要指定每個系統名稱。

欣賞任何提示/提示。

uj5u.com熱心網友回復：

您可以嘗試使用如下動態 SQL，我會使用條件聚合函式獲取資料透視值，然后我們可能會OUTER JOIN在動態 SQL 中添加或 EXISTS 條件。

我會使用sp_executesql而不是exec避免 sql 注入。

DECLARE @System_Name NVARCHAR(50) = 'IT' 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @parameter AS NVARCHAR(MAX);
    
SET @parameter = '@System_Name NVARCHAR(50)'

select DISTINCT System_Name
into #staging
from dbo.data t1
WHERE t1.System_Name <> @System_Name



SET @cols = STUFF((SELECT distinct ', SUM(IIF(System_Name = '''  System_Name ''',1,0))  * 100.0 / SUM(IIF(System_Name = @System_Name,0,1)) '   QUOTENAME(System_Name) 
            FROM #staging
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT SUM(IIF(System_Name = @System_Name,0,1)) [Distinct Count IT Key], '   @cols   ' from  dbo.data t1
 WHERE EXISTS (
  SELECT 1 
  FROM dbo.data tt 
  WHERE tt.Unique_Key = t1.Unique_Key
  AND tt.System_Name = @System_Name
 ) '
 
EXECUTE sp_executesql @query, @parameter,  @System_Name

sqlfiddle

uj5u.com熱心網友回復：

該方案改變了 PIVOT 本身的聚合功能。

首先，讓我們在#staging 中添加一列 [has_it]，用于跟蹤每個 Unique_Key 是否有 IT 行：

select Unique_Key, System_Name, case when exists(select 1 from data d2 where d2.Unique_Key=d1.Unique_Key and d2.System_Name='IT') then 1 else 0 end as has_it
into #staging
from data d1
where System_Name <> 'IT'
group by Unique_Key, System_Name

現在，該列的每個系統聚合（總和）除以所需的最終唯一鍵總數（示例 case=3）回傳請求數。將 PIVOT 更改為以下內容，它按原樣準備就緒，無需進一步查詢：

set @query = '  select * 
    from 
        (
            select System_Name,cnt as [Distinct Count IT Key],has_it*1.0/cnt as divcnt
            from #staging
            cross join
            (   
                select count(distinct Unique_Key) as cnt
                from dbo.data
                where System_Name = ''IT''
            )y
        ) x
        pivot 
        (
                sum(divcnt)
            for System_Name in ('   @cols   ')
        ) p'

uj5u.com熱心網友回復：

撰寫動態查詢時，您從非動態查詢開始。在轉換為動態查詢之前，請確保您獲得的查詢結果是正確的。

對于您需要的結果，查詢將是

with cte as
(
    select it.Unique_Key, ot.System_Name
    from   data it
           left join data ot on it.Unique_Key = ot.Unique_Key
                            and ot.System_Name <> 'IT'
    where  it.System_Name = 'IT'
)
select  [ITKey]    = count(distinct Unique_Key), 
        [ACCOUNTS] = count(case when System_Name = 'ACCOUNTS' then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key),
        [HR]       = count(case when System_Name = 'HR'       then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key),
        [PAYROLL]  = count(case when System_Name = 'PAYROLL'  then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key)
from   cte;

一旦你得到正確的結果，轉換為動態查詢就不難了。對那些重復的行使用 string_agg() 或用于 xml 路徑

declare @sql nvarchar(max);

; with cte as
(
    select distinct System_Name
    from   data
    where  System_Name <> 'IT'
)
select  @sql = string_agg(sql1   ' / '   sql2, ','   char(13))
from    cte
        cross apply
        (
            select sql1 = char(9)   quotename(System_Name)   ' = '
                          'count(case when System_Name = '''   System_Name   ''' then Unique_Key end) * 100.0 ',
                   sql2 = 'count(distinct Unique_Key)'
        ) a

select  @sql = 'with cte as'   char(13)
               '('   char(13)
               '    select it.Unique_Key, ot.System_Name'   char(13)
               '    from   data it'   char(13)
               '           left join data ot on it.Unique_Key = ot.Unique_Key'   char(13)
               '                            and ot.System_Name <> ''IT'''   char(13)
               '    where  it.System_Name = ''IT'''   char(13)
               ')'   char(13)
               'select [ITKey] = count(distinct Unique_Key), '   char(13)
               @sql   char(13)
               'from   cte;'   char(13)

print @sql;

exec sp_executesql @sql;

db<>小提琴演示

標籤：sql sql服务器

上一篇：組合左連接和選擇的語法錯誤

下一篇：在VisualStudioCode中卸載依賴于另一個擴展的擴展