Creating result sets with multiple values in a column

Question

I am using the AdventureWorks 2008 schema.

I am writing a procedure that returns the total sales by Sales Territory for sales people. There will be multiple sales people, so they need to be concatenated together in a comma separated list to show all sales people per record.

I have a solution below using cursors but I am wondering if there are any other ways of doing this (ie: cross apply, CTE's, etc).

if OBJECT_ID('tempdb..#tmpTerritorySalesPeople') is not null
    drop table #tmpTerritorySalesPeople
create table #tmpTerritorySalesPeople (TerritoryID int, TerritoryName varchar(50), TerritorySalesPeople varchar(2000))
GO

declare @territoryID int,
@territoryName varchar(50),
@salesPersonName varchar(100),
@salesPersonList varchar(2000)

--cursor for territy and tname
declare c_territory cursor for 
select st.territoryid, st.name
from sales.SalesTerritory st
order by st.territoryid

open c_territory
FETCH c_territory 
INTO @territoryid, @territoryname

while @@FETCH_STATUS = 0 
BEGIN 
    declare c_territorySalesPeople cursor for 
    -- cursor to hold list of sales people per territory
    select [SalesPersonName] = p.FirstName + ' ' + p.LastName
    FROM 
    sales.SalesPerson SP
    JOIN Person.person P
    ON SP.BusinessEntityID = P.BusinessEntityID
    JOIN Sales.SalesTerritory ST
    ON SP.territoryID = ST.TerritoryID
    where sp.territoryid = @territoryid
    order by st.territoryid 

    set @salesPersonList = ''

    open c_territorySalesPeople 
    fetch next from c_territorySalesPeople into @salesPersonName;

    while @@FETCH_STATUS = 0 
    begin
        if @salesPersonList = ''
            set @salesPersonList = @salesPersonName 
        else 
            set @salesPersonList = @salesPersonList + ', ' + @salesPersonName 

        fetch next from c_territorySalesPeople into @salesPersonName;
    end

    close c_territorySalesPeople 
    deallocate c_territorySalesPeople 

insert into #tmpTerritorySalesPeople 
select @territoryid, @territoryname, @salesPersonList
fetch next from c_territory
into @territoryid, @territoryname
end

close c_territory
deallocate c_territory
go

select  
[Sales Territory] = tsp.TerritoryName,
[Sales Person] = tsp.TerritorySalesPeople,
[TotalSalesAmount] = '$'+CONVERT(VARCHAR,sum(SOH.TotalDue),1)  
FROM 
sales.SalesOrderHeader SOH
JOIN #tmpTerritorySalesPeople tsp
ON SOH.TerritoryID = tsp.TerritoryID
where soh.TerritoryID is not null
group by tsp.TerritoryName, tsp.TerritorySalesPeople
order by tsp.TerritoryName
go

Source of data is Adventureworks.

Results of data:

Sales Territory,Sales Person,TotalSalesAmount
Australia,Lynn Tsoflias,"$11,814,376.10"
Canada,"Garrett Vargas, José Saraiva","$18,398,929.19"
Central,Jillian Carson,"$8,913,299.25"
France,Ranjit Varkey Chudukatil,"$8,119,749.35"
Germany,Rachel Valdez,"$5,479,819.58"
Northeast,Michael Blythe,"$7,820,209.63"
Northwest,"Pamela Ansman-Wolfe, David Campbell, Tete Mensa-Annan","$18,061,660.37"
Southeast,Tsvi Reiter,"$8,884,099.37"
Southwest,"Linda Mitchell, Shu Ito","$27,150,594.59"
United Kingdom,Jae Pak,"$8,574,048.71"

Consumable Sample data, and expected results will greatly help us help you. The fact that you are using a `CURSOR` already strongly suggests that you're doing something wrong, as I see no reason for it. A `CURSOR` is almost always the worst way to do something in an RDBMS, especially when you can achieve it with a set based solution. — Thom A, Aug 31 '19 at 18:43
Right, that is why I am posting this question. I am asking for advice on how to solve this query WITHOUT using a cursor — Jason, Aug 31 '19 at 19:00
If < 2017 , string aggregation is a small matter of stuff/xml . Not understanding your results of data, take a peek at https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server/17591536#17591536 — John Cappelletti, Aug 31 '19 at 19:27
*"Source of data is Adventureworks."* And what about those that don't have `AdventureWorks`? Please do include the sample data in your question; don't rely on us to (find and) download something from an off site resource. — Thom A, Aug 31 '19 at 19:37
When you add your sample data, could you also tag the version of SQL Server you're using, please? — Thom A, Aug 31 '19 at 19:37

score 0 · Accepted Answer · answered Aug 31 '19 at 20:21

You need to use STUFF() string function and FOR XML PATH to get CSV value column of territory wise sales persons.

You need to use CROSS apply to get territory wise total sales amount.

You need to use ISNULL() also for NULL value handle in any field.

Please check below query.

SELECT
ST.Name AS [Sales Territory],
STUFF(
(
SELECT
',' + ISNULL(PER.FirstName,'') + ' ' + ISNULL(PER.LastName,'')

---CONCAT(',',PER.FirstName,' ',PER.LastName) /*SQL SERVER 2012 Onwards*/

FROM sales.SalesPerson SSP
JOIN Person.person PER ON SSP.BusinessEntityID = PER.BusinessEntityID
AND SSP.territoryid = SP.territoryid
FOR XML PATH('')
),
1,1,'') AS [Sales Person],
TSalesAmt.[TotalSalesAmount] 
    FROM sales.SalesPerson SP
    JOIN Person.person P ON SP.BusinessEntityID = P.BusinessEntityID
    JOIN Sales.SalesTerritory ST ON SP.territoryID = ST.TerritoryID
CROSS APPLY
(
SELECT
SOH.TerritoryID,
'$'+CONVERT(VARCHAR,sum(SOH.TotalDue)) AS TotalSalesAmount
FROM sales.SalesOrderHeader SOH
Where SOH.TerritoryID = ST.TerritoryID
GROUP BY SOH.TerritoryID
) TSalesAmt
order by st.name

Creating result sets with multiple values in a column

1 Answers1