Combining multiple rows in SQL

Question

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

I want the SP value to be displayed in the same row where the Sprint_Name is not NULL. So I want my result to be like this

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

Here is my query

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

The problem I am facing is SP and Sprint_Name come from different tables. I thought of using Pivot function but that didnt work. Here is the query with 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

The result I get is

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

What if there is more than one row that has a non-null SP value? — Tab Alleman
– Tab Alleman, Commented Feb 13, 2015 at 17:58
I will limit that using where clause. For a unique SubsID there will be only one row which has non null SP Value — DataDr
– DataDr, Commented Feb 13, 2015 at 18:03

Tab Alleman · Accepted Answer · 2015-02-13 18:07:47Z

1

Use your regular query to get all the non-null SprintNames, and get SP with a sub-select.

pseudo-code:

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

In both places, replace "MyTable" with whatever collection of JOINs are needed to get the columns you need.

answered Feb 13, 2015 at 18:07

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DataDr Over a year ago

Thanks a lot, you are a life saver, I apologize but I cant upvote your answer since I dont have enough reputation.

Collectives™ on Stack Overflow

Combining multiple rows in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related