使用 PiVOT 将 SQL Server 行Microsoft到列转置

Question

>我正在使用MS-SQLServer-2016，并要求将行转置为列。我正在使用透视和动态SQL选项来做到这一点，因为行数是动态的。图 1 是我当前获得的输出。但是，客户端不希望显示这些 NULL。他只希望显示非空日期。有没有办法摆脱这些空值并仅显示不同的日期？

图1-Transpose_Output

问候，

狸猫

DECLARE @columns AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX), @var1 AS 
VARCHAR(MAX); 
SELECT @columns = stuff((select DISTINCT ',' + quotename(replace(replace(replace(n.action_note,' ','<>'),'><',''),'<>',' ') )
                         from engagement_action n, action_party m, personal p
                         where n.action_id = m.action_id
                           and p.party_id = m.party_id
                           and n.action_note like 'XXX'
                           and m.system_name = 'XXXXXXXXXXXX'
                           and p.customer_number = 'XXXXXXX' FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
SELECT @sql =
              'select "RANK_IN_PROP" ,
               customer_number, 
               customer_shortname, 
               system_name,
               '+ @columns + '
               from
                   (SELECT RANK() OVER (PARTITION BY convert(date,m.created_when) ORDER BY convert(date,m.created_when))"RANK_IN_PROP" ,
                          p.customer_number,
                          p.customer_shortname, 
                          m.system_name,
                          m.created_when ,
                          convert(date,m.created_when) as created_when, 
                          replace(replace(replace(n.action_note,'' '',''<>''),''><'',''''),''<>'','' '') as action_note1 
                   FROM engagement_action n, action_party m, personal p
                  WHERE n.action_id = m.action_id
                    AND p.party_id = m.party_id
                    AND n.action_note like ''%XXX%'' 
                    AND m.system_name = ''XXXXXXXXXXX''
                    AND p.customer_number = ''XXXXXXXX'' ) d
                  PIVOT 
                     (max(created_when) for action_note1 in ( ' + @columns + ' ))p order by created_when desc'
execute(@sql);`

Answer 1

你能试试这个吗，刚刚在 where 子句中添加(选择不同的action_note engagement_action，其中action_note不为空(。由于我无法创建测试数据，因此无法真正执行查询

SELECT @columns = stuff((select DISTINCT ',' + 
quotename(replace(replace(replace(n.action_note,' ','<>'),'><',''),'<>',' ') 
)
                     from 
                     (select distinct action_note engagement_action where action_note is not null) 
                     n, action_party m, personal p
                     where n.action_id = m.action_id
                       and p.party_id = m.party_id
                       and n.action_note like 'XXX'
                       and m.system_name = 'XXXXXXXXXXXX'
                       and p.customer_number = 'XXXXXXX' FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')