sql-server - 自定义列包含多个值
问题描述
这是我生成特定报告的查询代码。这可行,但是当我添加最后一个选择语句时
(Select Name
from RPT_CUSTOM_LIST_VALUES
where CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_10) AS [Application]
该RI1.Cust_10
列包含多个以逗号分隔的值。我怎样才能得到它,以便查找表提取每个值并为该值提供正确的名称?我无法在此数据库中创建或修改表。
select
RI1.incident_id as [Project Incident #],
(Select Name from RPT_CUSTOM_LIST_VALUES
where CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_02) as [Business Name],
RI1.NAME as [Project Name],
RI1.INCIDENT_STATUS_NAME as [Phase],
(Select Name from RPT_CUSTOM_LIST_VALUES
where CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_09) as [Key Milestone Name],
convert(nvarchar(10), RI1.CUST_26,103) as [Key Milestone Date], -- leave as date
convert(nvarchar(10), RI1.CUST_29,103) as [Target Completion Date], -- leave as date
RI1.SEVERITY_NAME as [Status Color],
RI1.CUST_01 as [Status Summary],
RI1.OWNER_NAME as [IT Owner],
(Select Name from RPT_CUSTOM_LIST_VALUES
where CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_10) AS [Application]
from
RPT_INCIDENTS RI1
where
RI1.PROJECT_ID = 445
and RI1.IS_DELETED = 0
and (RI1.INCIDENT_STATUS_NAME <> '5.1-Cancelled' and RI1.INCIDENT_STATUS_NAME <> '5.2-Completed')
但是,我的输出应该是最后一列应该有名称而不是值。这些值来自查找表,我需要一种方法来提取该数据,以便这些值现在是名称。
解决方案
没有您的数据,我无法进行测试,但希望它对您有用:
select RI1.incident_id as [Project Incident #]
, [Business Name] = s1.Name
,RI1.NAME as [Project Name]
,RI1.INCIDENT_STATUS_NAME as [Phase]
, [Key Milestone Name] = s2.Name
,convert(nvarchar(10), RI1.CUST_26,103) as [Key Milestone Date] -- leave as date
,convert(nvarchar(10), RI1.CUST_29,103) as [Target Completion Date] -- leave as date
,RI1.SEVERITY_NAME as [Status Color]
,RI1.CUST_01 as [Status Summary]
,RI1.OWNER_NAME as [IT Owner]
, [Application] = LEFT(s3.App,LEN(APP) - SIGN(LEN(s3.APP)))
From RPT_INCIDENTS AS RI1
OUTER APPLY (Select TOP 1 i.Name from RPT_CUSTOM_LIST_VALUES as i where i.CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_02) as s1
OUTER APPLY (Select TOP 1 i.Name from RPT_CUSTOM_LIST_VALUES as i where i.CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_09) as s2
OUTER APPLY (SELECT App = (
Select i.Name + ','
from RPT_CUSTOM_LIST_VALUES as i
where i.CUSTOM_PROPERTY_VALUE_ID = RI1.CUST_10
FOR XML PATH(''))
) as s3
where RI1.PROJECT_ID = 445
and RI1.IS_DELETED = 0
and (RI1.INCIDENT_STATUS_NAME <> '5.1-Cancelled' and RI1.INCIDENT_STATUS_NAME <> '5.2-Completed')
推荐阅读
- json - 如何提取转义的 JSON 值作为 Jmeter 中另一个外部 JSON 的值?
- amazon-web-services - AWS WAF 对 cloudfront 后面的 apigateway 没有影响
- microsoft-graph-api - 使用图形 api 在 Sharepoint/OneDrive 库中的文件夹中上传文件
- azure-devops - Azure DevOps 经典 CI 管道:签出是否隐含?
- dart - Dart 空安全 - 以空安全的方式从地图中检索值
- java - 如何关闭 PreparedStatement
- anylogic - 将类型 double 插入数据库
- python - 通过 id 解析 XML 中的特定项目
- c++ - Qt 5.15.2,C++ QString indexOf()函数问题
- php - 将命名参数传递给带有可变参数的内部函数时遇到问题