SQL comma delimited list as a single string

Question

In our business we have a main base account and then subordinate accounts under the baseaccount.
1.) How do I get all the accounts into a single column including the base account (comma delimited)?

I've used this code before on other datasets and it works great. I just can't figure out how to make this work with all the multiple joins.

    SELECT DISTINCT
    A.acctnbr as baseacctnbr, 
        STUFF((SELECT ', '+c1.ACCTNBR
FROM [USBI].[vw_FirmAccount] a1
    inner join [USBI].[vw_RelatedAccount] b1 on a1.firmaccountid = b1.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a2 on a2.firmaccountid = b1.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c1 on b1.relatedfirmaccountid = c1.firmaccountid
        where c1.AcctNbr = c.ACCTNBR
        FOR XML PATH ('')),1,1, '') AS ALLACCTS

FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid

where a.acctnbr = '11727765'
  and c.restrdate <> '99999999'
  and c.closerestrictind <> 'c'
  and c.iscurrent = '1'
  and b.iscurrent = '1'

My Output:

I would like to see the comma delimited list like this: 11727765, 11727799, 11783396, 12192670

I've gone through all the other questions on adding data to a single string and I can't find a solution here. Not a duplicate.

http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server . This might help. — Jigar, Apr 08 '16 at 22:20
use a scalar function, pass it the base account and select the subordinate accounts inside, concatenade it on a variable like select @result = @result+","+ .. — Byron, Apr 08 '16 at 22:22

score 0 · Answer 1 · answered Apr 08 '16 at 22:25

You can right click the box in the top left (to the left of baseacctnbr and above 1) and Save Results As a csv file, and open it with Notepad to view it that way.

Personally I use python a lot for this type of thing. With pyodbc (https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki) you can execute your query and access the data directly. For the following code, all you have to do is type the server and database name in the connection string

import pyodbc

connection = pyodbc.connect('DRIVER={SQL Server Native Client11.0};SERVER=YOURSERVERHERE;DATABASE=YOURDATABASENAMEHERE;TRUSTED_CONNECTION=yes')

cursor = connection.cursor()

query = """
SELECT DISTINCT
A.acctnbr as baseacctnbr, 
    STUFF((SELECT ', '+c1.ACCTNBR
FROM [USBI].[vw_FirmAccount] a1
    inner join [USBI].[vw_RelatedAccount] b1 on a1.firmaccountid = b1.basefirmaccountid 
inner join [USBI].[vw_FirmAccount] a2 on a2.firmaccountid = b1.relatedfirmaccountid
inner join USBI.vw_NameAddressBase c1 on b1.relatedfirmaccountid = c1.firmaccountid
    where c1.AcctNbr = c.ACCTNBR
    FOR XML PATH ('')),1,1, '') AS ALLACCTS

FROM [USBI].[vw_FirmAccount] a
inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid

where a.acctnbr = '11727765'
  and c.restrdate <> '99999999'
  and c.closerestrictind <> 'c'
  and c.iscurrent = '1'
  and b.iscurrent = '1'
"""
cursor.execute(query)
resultList = [row[0] for row in cursor.fetchall()]
print ", ".join(str(i) for i in resultList)

Byron · Answer 2 · 2016-04-08T22:45:18.207

try this create a scalar function, pass it all the parameters requiered to find subordinate accounts. The select @ResultVar =ResultVar +','+ .. will run all the row and ingrease the content on @ResultVar

CREATE FUNCTION ALLACCTS
(
    @baseacctnbr as varchar(100) 
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar as varchar(max)
    set @ResultVar  =''
    -- Add the T-SQL statements to compute the return value here
    SELECT @ResultVar =@ResultVar+','+ 
        STUFF((SELECT ', '+c1.ACCTNBR
    FROM [USBI].[vw_FirmAccount] a1
    inner join [USBI].[vw_RelatedAccount] b1 on a1.firmaccountid =     b1.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a2 on a2.firmaccountid =     b1.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c1 on b1.relatedfirmaccountid = c1.firmaccountid
        where c1.AcctNbr = c.ACCTNBR
        FOR XML PATH ('')),1,1, '') AS ALLACCTS

    FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid

    if(len(@ResultVar)>1)
        set @ResultVar = SUBSTRING(@ResultVar,1,len(@ResultVar)-1)
    -- Return the result of the function
    RETURN @ResultVar

END
GO

I'm getting the following errors when using your code. Msg 102, Level 15, State 1, Procedure ALLACCTS, Line 13 Incorrect syntax near 'STUFF'. Msg 156, Level 15, State 1, Procedure ALLACCTS, Line 19 Incorrect syntax near the keyword 'FOR'. — BIReportGuy, Apr 08 '16 at 22:39
The code seems to work, but now I get an error message that says: "CREATE FUNCTION permission denied in database 'USBI_DW'" Unfortunately, I don't have access to use the Create Function because I'm not in the technology group. Is there any other options? — BIReportGuy, Apr 10 '16 at 05:17
use a table variable to store the base account, with an identity, and with a while loop update each row, in the body of while put same body of the function, with the content of @ResultVar update de row, after that show the content of the table variable — Byron, Apr 12 '16 at 14:23

score 0 · Accepted Answer · answered Apr 13 '16 at 15:44

Here's what I did to resolve this issue with my query to create a comma delimited list on a single string or in a single column. I had to write the code in a CTE with a substring using the XML Path. It works for what I need and I'll use the code within an SSRS report.

 ;with t1 as
(
SELECT b.relatedfirmaccountid, b.basefirmaccountid, c.firmaccountid, a.acctnbr as baseacctnbr, a1.acctnbr as relatedacct,
rn = row_number() over (partition by a.acctnbr order by a.acctnbr),
    substring((select ', '+c.acctnbr as 'data()' 
FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid
where a.acctnbr = '11727765'
    and c.restrdate <> '99999999'
    and c.closerestrictind <> 'c'
    and c.iscurrent = '1'
    and b.iscurrent = '1'
for xml path('')),2,255) as "AllRelatedAccts"

FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid
where a.acctnbr = '11727765'
    and c.restrdate <> '99999999'
    and c.closerestrictind <> 'c'
    and c.iscurrent = '1'
    and b.iscurrent = '1'
 ),
 t2 as
 (
select distinct a.acctnbr, a.name
from USBI.vw_NameAddressBase a
where a.acctnbr = '11727765'
    and a.restrdate <> '99999999'
    and a.closerestrictind <> 'c'
    and a.iscurrent = '1'
group by a.acctnbr, a.name
  )
select 
    a.baseacctnbr,
    a.relatedacct,
    a.allrelatedaccts,
    a.rn
from t1 a
inner join t2 b on a.baseacctnbr = b.acctnbr
--where rn = 1

My Results:

My Results after changing my where rn = 1

SQL comma delimited list as a single string

3 Answers3