在SQL中合并多行

DataDr 发表于 Dev

数据表

SubsID   SUMMARY        SP    Sprint_Name    cfname     SourceID
10547      AA           6.0   NULL           Points     10543
10547     AA            NULL  GOE 10/03     Sprint      10543
10547     AA            NULL  GO 10/17      Sprint      10543

我希望SP值显示在Sprint_Name不为NULL的同一行中。所以我希望我的结果像这样

SubsID   SUMMARY        SP    Sprint_Name    cfname     SourceID
    10547     AA          6.0 GOE 10/03     Sprint      10543
    10547     AA          6.0  GO 10/17     Sprint      10543

这是我的查询

Select ji.ID as SubsID, ji.SUMMARY,  it.pname as IssueType, 
     cfv.NUMBERVALUE as SP, sp.NAME as Sprint_Name,
     cf.cfname, ISNULL(il.SOURCE,ji.ID) as SourceID
     from
    jiraissue as ji
    inner join customfieldvalue as cfv on cfv.ISSUE = ji.ID
    left outer join issuelink as il on il.DESTINATION = ji.ID or il.SOURCE = ji.ID 
    left outer join customfieldoption as cfo on cast (cfo.ID as varchar(1000)) = cfv.STRINGVALUE
    left outer join AO_60DB71_SPRINT as sp on cast (sp.ID as varchar(1000)) = cfv.STRINGVALUE 
    left outer join customfield as cf on cf.ID = cfv.CUSTOMFIELD

我面临的问题是SP和Sprint_Name来自不同的表。我想到了使用Pivot函数，但是没有用。这是使用Pivot进行的查询。

Select *
from 
( Select ji.ID as SubsID, ji.SUMMARY 
, cfv.NUMBERVALUE as SP, sp.NAME as Sprint_Name,
 cf.cfname, ISNULL(il.SOURCE,ji.ID) as SourceID
 from
jiraissue as ji
inner join issuestatus as st on ji.issuestatus = st.ID
inner join customfieldvalue as cfv on cfv.ISSUE = ji.ID
left outer join issuelink as il on il.DESTINATION = ji.ID or il.SOURCE = ji.ID 
left outer join customfieldoption as cfo on cast (cfo.ID as varchar(1000)) = cfv.STRINGVALUE
left outer join AO_60DB71_SPRINT as sp on cast (sp.ID as varchar(1000)) = cfv.STRINGVALUE 
left outer join customfield as cf on cf.ID = cfv.CUSTOMFIELD 
where  (il.LINKTYPE = 10200 or il.LINKTYPE is null) and it.pname <> 'Epic' 
) as SourceTable 
pivot
(max(SP)
for cfname IN ([Story Points])
) as PivotTable

我得到的结果是

SubsID  SUMMARY Sprint_Name SourceID    Story Points    
10547   AA      NULL        10543            6.0    
10547   AA      GO 10/17    10543           NULL
10547   AA      GOE 10/03   10543           NULL

塔布·阿勒曼

使用常规查询获取所有非空SprintName，并通过子选择获取SP。

伪代码：

SELECT SubsID, SprintName, SomeOtherColumns, 
  (SELECT TOP 1 SP FROM MyTable t2 WHERE t2.SubsID=t1.SubsID AND SP IS NOT NULL) AS StoryPoints
FROM MyTable t1
WHERE SprintName IS NOT NULL

在这两个地方，都用所需的JOIN集合替换“ MyTable”，以获取所需的列。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。