0

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.

I'm working towards a result of: ID0|NAME0|ID1|NAME1|ID2|NAME2...etc..

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?

whispers
  • 962
  • 1
  • 22
  • 48

2 Answers2

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.

Dave C
  • 7,272
  • 1
  • 19
  • 30
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.

stackFan
  • 1,528
  • 15
  • 22
  • Sorry... I thought the title would have cleared that up.. I am using SQL. It is NOT MySQL, or Oracle, just MS SQL – whispers Apr 06 '18 at 19:41
  • 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. – whispers Apr 06 '18 at 19:45
  • 1
    @whispers: "SQL" is a query language, not the name of a specific DBMS product. Every relational database is a "SQL database" –  Apr 06 '18 at 20:34