SQL数据透视表为不存在的子表返回NULL
SQL数据透视表为不存在的子表返回NULL
提问于 2011-03-12 08:59:52
我有一个典型的RDMS设置，其中主表中的记录可以通过M2M连接在相关表中包含可选记录。我试图透视这个数据，但在没有关系的情况下，我想返回一个默认值。下面的连接返回NULL。
select *
from
(
SELECT s.Biz_Name, la.Name AS Association, ISNULL(i.Location, 'Default') as Location
FROM dbo.ShopAssociations sa
INNER JOIN dbo.LookupAssociations la
ON sa.AssociationID = la.AssociationID
RIGHT JOIN dbo.Basic_Shop_Info s
ON sa.ShopID = s.ShopID
INNER JOIN dbo.Images i
ON la.ImageID = i.ImageID
) DataTable
PIVOT
(
min(Location)
for association in
([OnCall],[OCGuy],[ASCLogo],[ASC_OtherSt],[ASE],[AASP],[AASP_PA],
[ASE_BlueSeal],[AAA],[AAA-B],[ASA],[ATRA],[ICAR],[CAA],[ACDelco],
[Cert],[ASC],[BBB],[Goodyear],[Limos],[RVs],[Bosch],[NARSA],
[DiscTire],[BigO],[Tires],[Firestone],[ASCCA],[JustTires],[ASE_Blue])
) PivotTable输出如下所示：
BizName OnCall OCGuy ASCLogo ASC_OtherSt ASE ...
"Wonderful Biz" somevalue somevalue NULL somevalue NULL我试图实现的是，如果在从Basic_Shop_Info到ShopAssociations的内部连接中不存在子记录，我们将得到"Default“而不是NULL。我尝试过ISNULL()、Coalesce()甚至CASE语句，结果都是一样的。
回答 2
Stack Overflow用户
发布于 2012-08-18 06:16:41
根据你的评论，听起来你找到了一个解决方案。我回答这个问题只是为了提供一个建议，这个建议是基于你旋转了这么多列，而且它们都是硬编码的。您可以将动态SQL用于透视表，您的查询将如下所示：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name)
from dbo.LookupAssociations
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsPivot = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(Name) +', ''Default'')'
from dbo.LookupAssociations
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Bizname, ' + @colsPivot + ' from
(
SELECT s.Biz_Name, la.Name AS Association, ISNULL(i.Location, ''Default'') as Location
FROM dbo.ShopAssociations sa
INNER JOIN dbo.LookupAssociations la
ON sa.AssociationID = la.AssociationID
RIGHT JOIN dbo.Basic_Shop_Info s
ON sa.ShopID = s.ShopID
INNER JOIN dbo.Images i
ON la.ImageID = i.ImageID
) x
pivot
(
min(Location)
for association in (' + @cols + ')
) p
'
execute(@query)值@colsPivot将在您的每一列周围添加IsNull()，以便您可以将Default值放在适当的位置。但这应该会提供与原始查询相同的结果，其中所有内容都是硬编码的。
这将在运行时获得列的列表，因此您不必对任何内容进行硬编码，并且它将接受新值，而不必更改查询。
Stack Overflow用户
发布于 2020-07-02 03:13:03
我得到了这个：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10)))
from
(
select row_number() over(partition by person_nbr
order by person_nbr,first_name, last_name, medication_name) rn
from TA_PIVOT
) d
cross apply
(
select 'diag' col, 1 sort
) c
group by col, rn, sort
order by rn, sort
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT person_nbr, first_name, last_name,medication_name,' + @cols + '
from
(
select person_nbr,first_name,last_name,medication_name,
col+cast(rn as varchar(10)) col,
value
from
(
-- when you perform an unpivot the datatypes have to be the same.
-- you might have to cast the datatypes in this query
select person_nbr,first_name,last_name, medication_name, cast(icd_code_id as varchar(500)) diag,
row_number() over(partition by person_nbr order by person_nbr, first_name, last_name,medication_name) rn
from ta_pivot
) src
unpivot
(
value
for col in (diag)
) unpiv
) d
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute(@query);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5280055
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号