CODEWITHSUNDEEP
sqlsql-serverstringdelimited
whispers
whispers

Reputation: 962

MS SQL + output query as delimited string

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?

Upvotes: 0

Views: 543

Answers (2)

Dave C
Dave C

Reputation: 7392

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.

Upvotes: 1

stackFan
stackFan

Reputation: 1608

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.

Upvotes: 0

Related Questions