SQL combined columns-CodePudding

I would like to combined the output of the Chair and Co-Chair into one column but I am not sure how to do it. I did try concat the two fields Chair and Co-Chair but that didnt work. Any ideas how i can do this?

Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) 

CREATE TABLE #temp2 (
    University varchar(255),
    ChairRole varchar(255)
);


Insert into #temp2 (University ,ChairRole) VALUES ('Test 1','Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 2','Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 3','Vice-Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 4','Co-Chair')

SQL

select 
University,
Chair=isnull([Chair],0),
[Vice-Chair]=isnull([Vice-Chair],0),
[Co-Chair]=isnull([Co-Chair],0) 
from #temp2 
Pivot ( 
SUM(Total) 
for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair]) 
) as p 
where University is not null 
    ORDER BY 3 DESC 
    FOR XML PATH('University'),type) 
FOR XML PATH('Report')

Current Result

<University>
  <University>University/University>
  <Chair>0</Chair>
  <Vice-Chair>2</Vice-Chair>
  <Co-Chair>1</Co-Chair>
</University >

Result I am looking to do

<University>
  <University>University/University>
  <Chair>1</Chair>
  <Vice-Chair>2</Vice-Chair>
</University >

CodePudding user response：

Just Do a Concat using and add it up

select 
University,
Chair=isnull([Chair],0) isnull([Co-Chair],0) ,
[Vice-Chair]=isnull([Vice-Chair],0)
from #temp2 
Pivot ( 
SUM(Total) 
for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair]) 
) as p 
where University is not null 
    ORDER BY 3 DESC 
    FOR XML PATH('University'),type) 
FOR XML PATH('Report')

and if you don't see this is working, try with a SUb query like this

SELECT
    University,
    Chair = ISNULL(Chair,0) isnull([Vice-Chair],0),
    [Co-Chair]
    FROM
    (
        select 
        University,
        Chair=isnull([Chair],0),
        [Vice-Chair]=isnull([Vice-Chair],0),
        [Co-Chair]=isnull([Co-Chair],0) 
        from #temp2 
        Pivot ( 
        SUM(Total) 
        for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair]) 
        ) as p 
        where University is not null 
    )Q
    ORDER BY 3 DESC 
    FOR XML PATH('University'),type
FOR XML PATH('Report')