操作pivot函数生成的表数据

Question

在这里输入图像描述在上图中，cols Jan-21、Feb-21是动态生成的，数据是使用SQL中的pivot函数填充的。我想把0更新为No, 1更新为Yes。

我正在使用这个查询来获取数据。

表mon_year_data包含从1月21日到今天的日期动态生成的列名。在这里输入图像描述这是我们在下面的查询中使用的第二个表，输入图片描述

DECLARE @cols AS NVARCHAR(MAX)='',@cols2 AS NVARCHAR(MAX)='', @query AS NVARCHAR(MAX)=''
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(mon_year) from #mon_year_data FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'')
Select @cols2 = Coalesce(@cols2+',','') 
+ 'case when ' + replace(replace(QUOTENAME(mon_format),']',''),'[','') + '=''1'' then ''Received'' else ''Not Received'' end as ' 
+ replace(replace(QUOTENAME(mon_format),']',''),'[','') from (select (mon_format) from #first_four_columns group by mon_format) as tab
--print @cols2
set @query = 'SELECT PARTNER_NAME, CLIENT_PARTNER_CODE, POS, Inventory, [Started Reporting], ' + @cols2 + ' from 
( select f.partner_name,
f.client_partner_code, 
f.POS, f.Inventory, f.[Started Reporting], f.mon_format
from #first_four_columns f 
left join #mon_year_data myd 
on f.mon_format = myd.mon_year
) x
pivot 
(
count(x.mon_format)
for x.mon_format in (' + @cols + ')
) AS p '
--print @query
execute(@query)

我正在使用@cols2变量，但它不工作。

我无法在动态生成的列中操作pivot生成的数据。我需要将0和1替换为"否"one_answers"是"。请帮助。

打印语句的输出。

select  PARTNER_NAME, CLIENT_PARTNER_CODE, POS, Inventory, [Started Reporting], [Jan-21],[Feb-21],[Mar-21],[Apr-21],[May-21],[Jun-21],[Jul-21],[Aug-21],[Sep-21],[Oct-21],[Nov-21],[Dec-21],[Jan-22],[Feb-22],[Mar-22],[Apr-22],[May-22],[Jun-22],[Jul-22],[Aug-22],[Sep-22],[Oct-22],[Nov-22],[Dec-22] from 
( select f.partner_name,
f.client_partner_code, 
f.POS, f.Inventory, f.[Started Reporting], f.mon_format
from #first_four_columns f 
left join #mon_year_data myd 
on f.mon_format = myd.mon_year
) x
pivot 
(
count(x.mon_format)
for x.mon_format in ([Jan-21],[Feb-21],[Mar-21],[Apr-21],[May-21],[Jun-21],[Jul-21],[Aug-21],[Sep-21],[Oct-21],[Nov-21],[Dec-21],[Jan-22],[Feb-22],[Mar-22],[Apr-22],[May-22],[Jun-22],[Jul-22],[Aug-22],[Sep-22],[Oct-22],[Nov-22],[Dec-22])
) AS p 

完成时间:2022-12-27T21:26:51.1093350+00:00

Answer 1

我不相信有简单的方法。您只需要将CASE语句添加到最终选择列表中。

select ...
CASE WHEN [Jan-21] > 0 THEN 'Yes' ELSE 'No' END AS [Jan-21],
CASE WHEN [Feb-21] > 0 THEN 'Yes' ELSE 'No' END AS [Feb-21],
CASE WHEN [Mar-21] > 0 THEN 'Yes' ELSE 'No' END AS [Mar-21],
...

如果PIVOT允许在PIVOT表达式中使用更复杂的表达式，例如:

...
pivot (
CASE WHEN count(x.mon_format) > 0 THEN 'Yes' ELSE 'No' END  -- Not allowed.
for ...
)

，但这是不允许的。在这里，关于映射NULL值也有类似的问题，但答案只是将映射应用于每个单独的结果，有时使用动态SQL。

对于您的情况，例如:

DECLARE @mapped_cols AS NVARCHAR(MAX)=''
SELECT @mapped_cols = STUFF((
SELECT ', CASE WHEN '
+ QUOTENAME(mon_year)
+ ' > 0 THEN ''Yes'' ELSE ''No'' END AS '
+ QUOTENAME(mon_year)
from #mon_year_data
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1,'')

并在最终的动态SQL选择列表中包含@mapped_cols。