How to merge table rows-CodePudding

In my T-SQL I have following query:

SELECT 
     PivotTable1.CustomerProvince
     ,RTRIM(LTRIM(PivotTable1.MyBrand)) AS MyBrand
     ,PivotTable1.TCL AS TCLSales,
     PivotTable1.XVISION AS XVISIONSales
     ,PivotTable2.TCL AS TCLInstallation
     ,PivotTable2.XVISION AS XVISIONInstallation
FROM(
      SELECT CustomerProvince, SalesCount, Brand AS MyBrand, Brand FROM #TbTempFinalResult) SaleResults  
      PIVOT (SUM(SalesCount) FOR Brand IN (TCL,XVISION)) AS PivotTable1 
INNER JOIN 
      (SELECT CustomerProvince, InstallationCount, Brand,Brand AS MyBrand FROM #TbTempFinalResult) InstallationResults 
      PIVOT (SUM(InstallationCount) FOR Brand IN (TCL,XVISION)) AS PivotTable2 
ON PivotTable1.CustomerProvince = PivotTable2.CustomerProvince and PivotTable1.MyBrand = PivotTable2.MyBrand

and the result for this query is as below:

I must change upper table into following table:

How can I do this. Any help will be appriciated!

CodePudding user response：

You can try to use the aggregate function to get the value, then use STUFF function with self-join to make like string_agg.

you can use string_agg function if your sql-server version supported it.

;WITH CTE AS (
    SELECT 
         PivotTable1.CustomerProvince
         ,RTRIM(LTRIM(PivotTable1.MyBrand)) AS MyBrand
         ,PivotTable1.TCL AS TCLSales,
         PivotTable1.XVISION AS XVISIONSales
         ,PivotTable2.TCL AS TCLInstallation
         ,PivotTable2.XVISION AS XVISIONInstallation
    FROM(
          SELECT CustomerProvince, SalesCount, Brand AS MyBrand, Brand FROM #TbTempFinalResult) SaleResults  
          PIVOT (SUM(SalesCount) FOR Brand IN (TCL,XVISION)) AS PivotTable1 
    INNER JOIN 
          (SELECT CustomerProvince, InstallationCount, Brand,Brand AS MyBrand FROM #TbTempFinalResult) InstallationResults 
          PIVOT (SUM(InstallationCount) FOR Brand IN (TCL,XVISION)) AS PivotTable2 
    ON PivotTable1.CustomerProvince = PivotTable2.CustomerProvince and PivotTable1.MyBrand = PivotTable2.MyBrand
)
SELECT CustomerProvince,
       STUFF((
    SELECT ', '   MyBrand
    FROM  CTE tt
    WHERE tt.CustomerProvince = t.CustomerProvince 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') MyBrand,
       MAX(TCLSales) TCLSales,
       MAX(XVISIONSales) XVISIONSales,
       MAX(TCLInstallation) TCLInstallation,
       MAX(XVISIONInstallation) XVISIONInstallation
FROM CTE t
GROUP BY CustomerProvince

sqlfiddle