Search code examples
sqlsql-serverodbckepserverex

Select bottom n records and concatenate in same row

I have a select n bottom records in table and return a concatenate values in same row. This is code is ok but not return n bottom records:

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(Val1, '') + '; ' +
            ISNULL(Val2, '') + '; ' +
            ISNULL(Val3, '') + '; ' +
            ISNULL(Val4), '')
        FROM Table_x
    FOR XML PATH ('')), 1, 2, '') AS val;

I writed other query return n bottom recods but I need to use KEPServerEX with ODBC conections and it only support SELECT and EXECUTE:

DECLARE @max_id INT
SELECT @max_id = MAX(id) FROM table_x
SET @max_id = @max_id - 20
SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT *
            FROM table_x
            WHERE id > @max_id
        ) AS Latest_rec
    FOR XML PATH ('')), 1, 2, '') AS val;

I need to have a long word of the return. Ex.: val1;val2 ; val3; val4; val1; val2; val3; val4.

I use Microsoft SQL Server 2012 (SP3) - 11.0.6020.0 (X64) Express Edition (64-bit). But the problem is KEPServerEX which only accepts SELECT and EXECUTE.

enter image description here

In moment, i not find other query return n bottom records in same row supported by KEPServerEX.

Solution

  • Select last 20 rows when ordered by id. Return in ASC order, with a single query

    SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT top(20) *
            FROM table_x
            ORDER BY id DESC
        ) AS Latest_rec
        ORDER BY id ASC
        FOR XML PATH ('')), 1, 2, '') AS val;