mysql function return multiple value in 1 string

Question

can Mysql return multiple cell in 1 string ??

in sqlserver I can make function like this

CREATE FUNCTION [dbo].[getTribunByOrder]
(
    @idtrorder varchar(10)
)
RETURNS varchar(1000)
AS
BEGIN
    declare @trtribun varchar(1000)
    set @trtribun = ''
    select  @trtribun = @trtribun + nametribun +'='+ convert(varchar(3),jumlah) + ','
            from trtribun b
            where idtrorder = @idtrorder
    set @trtribun = substring(@trtribun, 0, len(@trtribun))
    RETURN @trtribun
END

return of this function = "name1=1,name2=3,name3=2"
can mysql create function like this ??

If you're going to feed data to some code, you're way better off fetching data as-is, building the string in code. — Alex
– Alex, Commented Oct 8, 2013 at 9:21
this function will be called in a stored procedure, I want my programme just call 1 stored procedure instead called other store procedure thousand times :| — GrandAlienz
– GrandAlienz, Commented Oct 8, 2013 at 9:25

Sal00m · Accepted Answer · 2013-10-08 09:31:27Z

3

Of course you can:

Using CONCAT for example you can return multiple columns in one row:

SELECT CONCAT(column1,column2,...) FROM ...

And using GROUP_CONCAT you can return multiple rows in one row

SELECT GROUP_CONCAT(column1) FROM... WHERE... GROUP BY column1

Take a look here http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat and here http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

answered Oct 8, 2013 at 9:31

Sal00m

2,9143 gold badges24 silver badges33 bronze badges

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

2 Comments

GrandAlienz Over a year ago

wow thx its help, but why 1st row comes in end ?? the return is "row2, row3,..., rown, row1". I already use order by and its works well, but not with group_concat. any idea ??

GrandAlienz Over a year ago

oh i see.. order by works in group_concat(), so I must group_concat(column1 order by column2) to make it works. thx sal00m

Collectives™ on Stack Overflow

mysql function return multiple value in 1 string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related