CODEWITHSUNDEEP
sqlsql-server
MadBoy
MadBoy

Reputation: 11104

Returning oneliner from SQL Server query

I use this code to return many rows as one row in SQL Server 2008. It works nicely and returns numbers that are divided with ;. But it's also true when there's only one row to return and then I don't really need ; to be shown at all. Same applies to having ; for last value. What's the best way to fix it without impacting performance of this query? 

SELECT (        
    SELECT [KontaktyWartosc] + '; '  FROM [PodmiotyKontakty]
    INNER JOIN [Klienci]
    ON [PodmiotyKontakty].[PodmiotID] = [Klienci].[PodmiotID] 
    WHERE [KlienciID] = @idKlienta AND
    FOR XML PATH('')) AS CONTENT
)

Upvotes: 0

Views: 49

Answers (1)

gbn
gbn

Reputation: 432311

Just reverse the order of the concatenation and use SUBSTRING.
8000 avoids an extra LEN and you can use 2000000000 for (max) types too

SELECT
    SUBSTRING (
        (        
            SELECT '; ' + [KontaktyWartosc]   FROM [PodmiotyKontakty]
            INNER JOIN [Klienci]
            ON [PodmiotyKontakty].[PodmiotID] = [Klienci].[PodmiotID] 
            WHERE [KlienciID] = @idKlienta AND
            FOR XML PATH('')) AS CONTENT
        ), 3, 8000)

Upvotes: 2

Related Questions