0

I have a table in a SQL-Server database (SQL-Server 2016) with 2 fields:

ConcateCode 

ID int,
Random nchar(5)

ID  Random

1   A2dd4
2   2f4mR
3   dhu9q
4   0lpms

Now I need a query where I get all the Random-fields concatenated in one field from Start-ID to End-ID, e.g. something like

SELECT ConcateCode FROM Table WHERE ID >= 1 AND ID < 5

and returns A2dd42f4mRdhu9q0lpms.

The problem is that I can't use a StoredProcedure, because my Programming-Language doesn't support Stored-Procedures, but only direct table access or queries.

The question sounds stupid easy, but I try to solve the problem now for a week and get no solution. Hope someone is more intelligent than me.

Marko Ivkovic
  • 1,262
  • 1
  • 11
  • 14
JimKnopf
  • 11
  • 1
  • 1
    The principle here is identical to creating a comma delimited value in SQL server, you just have a zero length string (`''`) as the delimiter. What were your attempts, why didn't they work? – Thom A Jun 09 '21 at 13:25

2 Answers2

1

Try this:

DECLARE @MyTable TABLE (ID int, Random nchar(5))
INSERT INTO @MyTable VALUES 
        ( 1, 'A2dd4')
    ,   ( 2, '2f4mR')
    ,   ( 3, 'dhu9q')
    ,   ( 4, '0lpms')
;
DECLARE @StartID    int = 1
    ,   @EndID      int = 5
;
DECLARE @String varchar(max) = (SELECT ''+Random FROM @MyTable WHERE ID BETWEEN @StartID AND @EndID FOR XML PATH('') );

SELECT @String;
Andy3B
  • 444
  • 2
  • 6
  • 1
    Thx Andy3B, but how do I get this in a view? In my programming environment only I can only use tables and views. – JimKnopf Jun 09 '21 at 14:28
  • You cannot pass parameters to a view (i.e. @StartID and @EndID). What about to create a scalar function? – Andy3B Jun 10 '21 at 20:47
1
DECLARE @Result VARCHAR(MAX)
DECLARE @StartID    int = 1
,   @EndID      int = 5

SELECT @Result= COALESCE(@Result, '') + ConcateCode FROM Table
WHERE ID BETWEEN @StartID AND @EndID
SELECT @Result
Erfan Mohammadi
  • 424
  • 3
  • 7
  • 2
    Using variable assignment to concatenate strings (SELECT @Column += ....) is [undocumented and unreliable](https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/). It does not always work as expected. You should either use `STRING_AGG()` (if using a version that supports it), or use [XML Extensions](https://stackoverflow.com/a/5031297/1048425) to concatenate the rows into a variable – GarethD Jun 09 '21 at 13:46