SQL Server inner joins

Question

Can any one help on this like"want the 2 nd row also in first row"..

SELECT  BDSReleaseBDDSBPD.ProjectID,
        BDSFlowDetails.Flowid,IterationID, 
        case when ReleaseTask = 'BDDS' then UID end as 'BDDS UID' ,
        case when ReleaseTask = 'BDDS' then  Concat(FirstName,'_',Lastname) end as BDDSReleasedBy, 
        case when ReleaseTask = 'BDDS' then ReleaseDate end as 'BDDS Released Date' ,
        case when ReleaseTask = 'BPD' then UID end as 'BPD UID' ,
        case when ReleaseTask = 'BPD' then  Concat(FirstName,'_',Lastname) end as BPDReleasedBy, 
        case when ReleaseTask = 'BPD' then ReleaseDate end as 'BPD Released Date' 
FROM BDSReleaseBDDSBPD 
inner join BDSFlowDetails on BDSFlowDetails.FlowID = BDSReleaseBDDSBPD.FlowID 
inner join BDSUserProfile on BDSUserProfile.UserName=BDSReleaseBDDSBPD.UserName 
WHERE BDSReleaseBDDSBPD.ProjectID = 861
order by BDSFlowDetails.Flowid

[SQL result set..] [![enter image description here][1]][1]

Answer 1

You can pivot on the ProjectID, Flowid, and IterationID columns, using an aggregation to combine the records:

SELECT  BDSReleaseBDDSBPD.ProjectID,
        BDSFlowDetails.Flowid,
        IterationID, 
        MAX(CASE WHEN ReleaseTask = 'BDDS' THEN UID END) AS 'BDDS UID',
        MAX(CASE WHEN ReleaseTask = 'BDDS'
                 THEN CONCAT(FirstName, '_', Lastname) END) AS BDDSReleasedBy, 
        MAX(CASE WHEN ReleaseTask = 'BDDS'
                 THEN ReleaseDate END) AS 'BDDS Released Date' ,
        MAX(CASE WHEN ReleaseTask = 'BPD' THEN UID END) AS 'BPD UID' ,
        MAX(CASE WHEN ReleaseTask = 'BPD'
                 THEN CONCAT(FirstName, '_', Lastname) END) AS BPDReleasedBy, 
        MAX(CASE WHEN ReleaseTask = 'BPD'
                 THEN ReleaseDate END) AS 'BPD Released Date' 
FROM BDSReleaseBDDSBPD 
INNER JOIN BDSFlowDetails
    ON BDSFlowDetails.FlowID = BDSReleaseBDDSBPD.FlowID 
INNER JOIN BDSUserProfile
    ON BDSUserProfile.UserName = BDSReleaseBDDSBPD.UserName 
WHERE BDSReleaseBDDSBPD.ProjectID = 861
GROUP BY BDSReleaseBDDSBPD.ProjectID,
         BDSFlowDetails.Flowid,
         IterationID 
ORDER BY BDSFlowDetails.Flowid