CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Muflix
Muflix

Reputation: 6798

How to simulate STRING_AGG (contrary of split) function

How to simulate string_agg function ?

I need get this 

[value]
1
2
3

into this

1,2,3

I tried following

CREATE TYPE stringArray AS TABLE ([value] nvarchar(255))
GO

CREATE FUNCTION dbo.ufn_join 
(
    @table stringArray readonly, 
    @separator nvarchar(5) = ','
)
    RETURNS nvarchar(max)
AS
BEGIN
    RETURN stuff((select @separator + value from @table for xml path('')), 1, 1, '')
END
GO

SELECT dbo.ufn_join(
    (
    SELECT cast(1 as nvarchar(255)) as value
    UNION
    SELECT cast(2 as nvarchar(255)) as value
    UNION
    SELECT cast(3 as nvarchar(255)) as value
    )
    , DEFAULT
)

but I am getting an error

-- Error: Operand type clash: nvarchar is incompatible with stringArray

Only condition is that i do not want to use any kind of variables. CLR function is also totally fine, but there i have the same issue, how to insert return of select as a parameter to the function.

Upvotes: 1

Views: 12577

Answers (3)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

Normally I use this link when I want to concat rows. There are several options how to do it, so here you can find inspiration on which approach you like the most. Be aware of XML PATH since it uses all of your CPU Processes and can max out your CPU to 100%.

Different concat approaches

Example from the link:

CREATE FUNCTION dbo.udf_select_concat ( @c INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
       SET @p = '' ;
    SELECT @p = @p + ProductName + ','
      FROM Northwind..Products
     WHERE CategoryId = @c ;
RETURN @p
END


SELECT CategoryId, dbo.udf_select_concat( CategoryId )
FROM Northwind..Products
GROUP BY CategoryId ;

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7928

TVP issue aside, your function will be profoundly faster and more efficient by turning it into an inline table valued function (commonly referred as an inline scalar function (iSF)). This article explains what I'm saying in detail: How to Make Scalar UDFs Run Faster (SQL Spackle)

CREATE FUNCTION dbo.ufn_join (@separator nvarchar(5))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT concatinatedTxt = 
  stuff((select @separator + someTxt from dbo.someTable for xml path('')), 1, 1, '');

Upvotes: 2

PreQL
PreQL

Reputation: 358

It's because you declared a type, set that type to a parameter and the tried to insert a table into this parameter (a different type).

Try this:

CREATE TYPE stringArray AS TABLE ([value] nvarchar(255))
GO

CREATE FUNCTION dbo.ufn_join 
(
    @table stringArray readonly, 
    @separator nvarchar(5) = ','
)
    RETURNS nvarchar(max)
AS
BEGIN
    RETURN stuff((select @separator + value from @table for xml path('')), 1, 1, '')
END
GO

DECLARE @table stringArray

INSERT INTO @Table
SELECT cast(1 as nvarchar(255)) as value
UNION
SELECT cast(2 as nvarchar(255)) as value
UNION
SELECT cast(3 as nvarchar(255)) as value

SELECT dbo.ufn_join(
    @Table
    , DEFAULT
)

Upvotes: 1

Related Questions