CODEWITHSUNDEEP
sqlsql-serverselect
Weetobix
Weetobix

Reputation: 35

Select statement within a select statement

I have the following select statement that returns exactly what I want:

DECLARE @result varchar(max) = ''

SELECT @result += (result.Fullname + '<br/>') 
FROM (SELECT DISTINCT Fullname
FROM Providers 
WHERE Status='A') as result

select substring(@result, 0, len(@result) - 4)

The only problem is, I want the output from this query to be displayed as a column entry from a larger select statement.

Eg. 

SELECT      
    Column AS [AColumnName],

    SELECT @result += (result.Fullname + '<br/>') 
    FROM (SELECT DISTINCT Fullname
    FROM Providers 
    WHERE Status='A') as result

    select substring(@result, 0, len(@result) - 4) as [LenderList]
FROM
 Table

But I am currently getting the error: Incorrect syntax near the keyword 'SELECT'. The error pointing to line 4

Any ideas?

Upvotes: 0

Views: 5574

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You need aggregate string concatenation in SQL Server. There are already many answers on the subquery, but to save you the trouble:

SELECT Column AS [AColumnName],
       STUFF((SELECT DISTINCT '<br/>' + Fullname
              FROM Providers 
              WHERE Status = 'A'
              FOR XML PATH (''), TYPE
             ).value('.', 'varchar(max)'
                    ), 1, 5, ''
            ) as result
FROM Table;

The use of the type is important because your string has special XML characters.

Upvotes: 2

Steinwolfe
Steinwolfe

Reputation: 238

Which Database? If you can use for xml, then something like...

select substring(a.innards, 0, len(a.innards) - 4) as [LenderList]
from 
(
    SELECT innards = STUFF(
        (SELECT DISTINCT Fullname + '</br>'
        FROM Providers
        WHERE [Status] = 'A'
        FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)')
        , 1
        , 0
        , '')
) a

Upvotes: 0

lysp
lysp

Reputation: 36

Can you simply run it in 2 statements?

SELECT @result += (result.Fullname + '<br/>') 
FROM (SELECT DISTINCT Fullname
     FROM Providers 
     WHERE Status='A') as result

SELECT      
    Column AS [AColumnName],
    substring(@result, 0, len(@result) - 4)
FROM Table

Upvotes: 0

Related Questions