Function that returns multiple columns

Question

I am creating a function that splits up strings. I want to pass the function to a string and get several rows back (the amount of rows would be dynamic depending on the length of the string).

I was thinking of possibly using a table function and joining this to my query.

I am NOT asking for this to be written for me, I am simply wondering if this is even possible in SQL Server 2014, and what the best approach would be if it is.

So the string 'ABC' would be returned as:

COL1  COL2  COL3
A      B    C

Yeah that's what I've been looking at, I mention it above. Could it be used for splitting a string and returning each character in a separate column dynamically? — Alec.
– Alec., Commented Jun 12, 2015 at 13:22
No - the columns must be static. You can return multiple rows, though - see my answer. — D Stanley
– D Stanley, Commented Jun 12, 2015 at 13:26
@DStanley Of course it can be done, but you might need dynamic SQL. — SQL Police
– SQL Police, Commented Jun 12, 2015 at 16:47
@SQLPolice Of course it can't - not from a table-valued function. The columns that it returns are part of the definition. From MSDN: "The RETURNS clause also defines the format of the table. " — D Stanley
– D Stanley, Commented Jun 15, 2015 at 12:55

D Stanley · Accepted Answer · 2015-06-12 13:25:28Z

2

I am simply wondering if this is even possible in SQL Server 2014

Yes, you can create table-valued user defined functions that return, well, a table value.

So the string 'ABC' would be returned as:
COL1  COL2  COL3
A      B    C

Well, now you're in trouble - table-valued functions must have a return value with a pre-defined schema, so you can't dynamically set the column names. You could return those as rows, though:

Item  Value
1     A
2     B
3     C

answered Jun 12, 2015 at 13:25

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alec. Over a year ago

Perhaps returning as rows then apply a dynamic pivot could get me the result I'm after. This is going to be hideous...

D Stanley Over a year ago

Yes dynamic pivots are no fun either.

Giorgi Nakeuri · Accepted Answer · 2015-06-12 13:40:09Z

You didn't mentioned where do you want to apply this but there are solutions:

DECLARE @t TABLE(id int, n VARCHAR(50))
INSERT INTO @t VALUES
(1, 'ABCDEF'),
(2, 'EFGHIJKLMNOPQ')


;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM @t
 UNION ALL 
 SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)

SELECT *
FROM cte 
PIVOT (MAX(c) FOR ind IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[12],[13],[14],[15])) p

Output:

id  n               1   2   3   4   5   6   7    8    9    10   12   13   14    15
1   ABCDEF          A   B   C   D   E   F   NULL NULL NULL NULL NULL NULL NULL  NULL
2   EFGHIJKLMNOPQ   E   F   G   H   I   J   K    L    M    N    P    Q    NULL  NULL

Here is dynamic version:

DECLARE @l INT, @c VARCHAR(MAX) = ''
SELECT @l = MAX(LEN(n)) FROM PivotTable

WHILE @l > 0
BEGIN
 SET @c = ',[' + CAST(@l AS VARCHAR(MAX)) + ']' + @c
 SET @l = @l - 1
END

SET @c = STUFF(@c, 1, 1,'')

DECLARE @s NVARCHAR(MAX) = '
;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM PivotTable
 UNION ALL 
 SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)

SELECT *
FROM cte 
PIVOT (MAX(c) FOR ind IN(' + @c + ')) p'

EXEC (@s)

Collectives™ on Stack Overflow

Function that returns multiple columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related