Need to join rows result in one row in SAP Business One

[krishnam_mathur]

Hi Team,

I need help to join the result of this query, in one row.

select t1.DocNum from vpm2 t0 inner join opch t1 on t0.DocEntry=t1.DocEntry where t0.docnum=1755.

Right now the result of this query gives 4 rows. I need to join all 4 t1.docnum sepeated by ', '.

Can anyone please help..

[azizelmir · Accepted solution]

Hi Krishnam,

This could be achieved by the proposed solution:

1- Create a function that return multiple values in one column.

USE [databaseName]
GO
/****** Object:  UserDefinedFunction [dbo].[GetAliasesByACC]    Script Date: 08/02/2019 9:38:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetAliasesByACC]
( @AccID int)
RETURNS nvarchar(max)
AS
BEGIN
    Declare@output nvarchar(max)
    Select @output = COALESCE(@output + '.', '') + convert(nvarchar,t1.Docnum)
       from vpm2 t0 inner join opch t1 on t0.DocEntry=t1.DocEntry  where t0.docnum=@AccID 
    return @output
END

2- Run the below code to get your ultimate goal:

SELECT Distinct Docnum,dbo.GetAliasesByACC(docnum) as 'Result' FROM vpm2 WHERE docnum =1755

Hope this information helps you.

Thank you,

Aziz El Mir