MS SQL + output query as delimited string

Question

I'm not sure if I am not understanding the example found correctly.. or if I'm doing something else wrong here... but I am trying to get a my query results returned as:

1.) One long delimited string 2.) Remove the trailing delimiter at the end of the returned data

Table has several columns, but I am ONLY grabbing the ID and the COMPANY NAME.

Using this:

SELECT (ID + '|' + COMPANY + '|') AS ORGLIST 
FROM vw_PreferredMail
WHERE member_type = 'CTR' 
ORDER BY ID

I am getting is:

ID0|NAME0 ID1|NAME1 ID2|NAME2

all in their own rows..

Using this:

DECLARE @OrgResults varchar(255)
SELECT @OrgResults = ID + '|' + COMPANY
FROM vw_PreferredMail
WHERE member_type = 'CTR' 
ORDER BY ID 
SELECT SUBSTRING(@OrgResults, 1, LEN(@OrgResults) - 1);

I only get the LAST row returned..

How can I get it all to be in one delimited 'row'.. (while trimming off the last delimiter? or first if it needs to be changed around?)

Update:

This approach 'seems' to work.. (checking SSMS currently).. before adding my real project

SELECT (ID + '|' + COMPANY)
FROM vw_PreferredMail
WHERE member_type = 'CTR'
ORDER BY ID 
FOR XML PATH('');

The results, look like a 'link' in SSMS (SQL Server Management Studio).. but seem to be complete..

Is this acceptable usage above?

Do you use Oracle or SQL Server or another one?

Meow Meow
– Meow Meow

2018-04-06 19:25:53 +00:00
Commented Apr 6, 2018 at 19:25 — Meow Meow
– Meow Meow, Commented Apr 6, 2018 at 19:25

Dave C · Accepted Answer · 2018-04-07 01:16:58Z

1

You were close originally; simple mistake, you're not retaining the value of @OrgResults, your replacing it.

You're doing @OrgResults=..., not @OrgResults=@OrgResults+.... You also need to set @OrgResults='' before you start concatenating, or else you'll wind up with a NULL result.

Quick fix:

DECLARE @OrgResults varchar(255)
SET @OrgResults=''

SELECT @OrgResults = @OrgResults + ID + '|' + COMPANY + '|'
FROM vw_PreferredMail
WHERE member_type = 'CTR' 
ORDER BY ID 

SET @OrgResults = LEFT(@OrgResults, LEN(@OrgResults)-1);
SELECT @OrgResults

Be warned, 255 isn't much, you might start truncating. You might need to stretch that out.

answered Apr 7, 2018 at 1:16

Dave C

7,4821 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fcbgp · Accepted Answer · 2018-04-06 19:29:23Z

0

You have not specified which SQL you are using but if it was MySQL I'd do following to get results in same row with the formatting you wanted:

SELECT concat(concat(concat(id ,"|"),COMPANY ),"|") AS ORGLIST 
FROM vw_PreferredMail
WHERE member_type = 'CTR' 
ORDER BY ID

Let us know your requirement and SQL version in detail so that we can modify.

answered Apr 6, 2018 at 19:29

fcbgp

1,60816 silver badges22 bronze badges

3 Comments

whispers Over a year ago

Sorry... I thought the title would have cleared that up.. I am using SQL. It is NOT MySQL, or Oracle, just MS SQL

whispers Over a year ago

MS SQL I'm not sure of the version off hand.. but my SSMS install is quite old SSMS 2005 still.. should be upgraded by end of month department says!).. CONCAT is not a valid function at this time.. (changing this to just be "+".. gives same results as my initial example posted above. adding the XML PATH() gets me a list like my update above.

user330315 Over a year ago

@whispers: "SQL" is a query language, not the name of a specific DBMS product. Every relational database is a "SQL database"

Collectives™ on Stack Overflow

MS SQL + output query as delimited string

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related