Post Processing Result Set with Dynamic Query Input

[Former Member]

I have a requirement to format the results of any SELECT statement so all columns and all rows are returned as a single field. Due to how this is integrating with vendor software, I cannot pre or post-process the query outside of this function.

As an example, say we have a query like this:

SELECT first_name, member_since, last_seen FROM Person

The result set would normally look something like this:

===============================================
| first_name  |  member_since  |  last_seen   |
===============================================
|  Glenn      |   2009-11-10   |   2012-02-01 |
|  Breck      |   2009-11-08   |   2012-01-31 |
|  Volker     |   2009-11-11   |   2012-01-30 |

The data format standard dictates each row should be enclosed in square brackets and separated by commas. Each column should be separated by a '^' character. Therefore, if I call my function like this:

SELECT query_to_dataformat( 'SELECT first_name, member_since, last_seen FROM Person' );

The result should be:

'[Glenn^2009-11-10^2012-02-01],[Breck^2009-11-08^2012-01-31],[Volker^2009-11-11^2012-01-30]'

This same thing needs to work, regardless of the original query's complexity, number of columns, or column names. My initial thought on this task was to use two techniques:

1. Run the query directly and loop over the results using "FOR ... USING input_sql". This way, cursor variables are created automatically, regardless of how many there are and what they are named. There is no need to use DECLARE or FETCH, which I wouldn't know beforehand anyways.
2. Use a cursor that iterates over "... sa_describe_query( input_sql )" so I am able to figure out how many columns are in the row and what their names are.

Unfortunately, I am in a position where I have a variable holding a variable name and no direct way to reference the actual value. Here is my existing code (Note: this will produce extra commas and carets, but I removed the additional code that deals with those issues to make it more brief):

ALTER FUNCTION "DBA"."query_to_dataformat"( IN input_sql LONG VARCHAR )
RETURNS LONG VARCHAR
BEGIN
    DECLARE @dataformat_string LONG VARCHAR;
    DECLARE @column_number     INTEGER;
    DECLARE @column_name       VARCHAR(128);
    DECLARE @column_list DYNAMIC SCROLL CURSOR FOR
        SELECT column_number, name FROM sa_describe_query( input_sql );

    -- Execute input SQL and loop over results
    FOR results_loop AS results CURSOR USING input_sql
    DO
        -- Every row begins with a '[' character
        SET @dataformat_string = STRING( @dataformat_string, '[' );

        -- Loop over each column in the result set
        OPEN @column_list;
        column_loop: LOOP
            FETCH NEXT @column_list INTO @column_number, @column_name;

            -- Leave loop if we processed all columns
            IF ( SQLCODE <> 0 ) THEN LEAVE column_loop; END IF;

            -- Append column data to string; Columns are separated by '^'
            SET @dataformat_string = STRING( @dataformat_string, '^', @column_name );

        END LOOP;
        CLOSE @column_list;

        -- Every row ends with a ']' character
        SET @dataformat_string = STRING( @dataformat_string, '],' );
    END FOR;

    -- Return final string required data format
    RETURN @dataformat_string;
END

In the above example, @column_name would be holding "first_name", "member_since", or "last_seen". That is the variable name I need to reference from the FOR statement, but it doesn't seem possible to resolve it to the actual local FOR variable, which is holding "Breck" or "Glenn".

This results in the output string of the following:

'[first_name^member_since^last_seen],[first_name^member_since^last_seen],[first_name^member_since^last_seen]'

Am I going about this the wrong way? Is there a simpler solution I am missing?

Environment:

OS: Windows Server 2008 64bit Database: SQL Anywhere 11.0.1.2724

CPU: 2x Intel Xeon 10-Core RAM: 64GB Disk: 320GB FusionIO (database), 640GB FusionIO (log/temp)

[VolkerBarth]

Just a hint:

The LIST() aggregate may be helpful to "flatten" the multiple columns into one string, and you could use "^" as the according delimiter...

[Breck_Carter · Accepted solution]

You did ask for simple, right? ...just so I have that right 🙂

CREATE TABLE person (
   first_name    VARCHAR ( 100 ),
   member_since  DATE,
   last_seen     DATE );

INSERT person VALUES ( 'Glenn',  '2009-11-10', '2012-02-01' );
INSERT person VALUES ( 'Breck',  '2009-11-08', '2012-01-31' );
INSERT person VALUES ( 'Volker', '2009-11-11', '2012-01-30' );
COMMIT;

CREATE FUNCTION query_to_dataformat (
   IN @select LONG VARCHAR )
   RETURNS  LONG VARCHAR 
BEGIN
   DECLARE @unload LONG VARCHAR;
   DECLARE @result LONG VARCHAR;
   SET @unload = STRING (
      'UNLOAD ',
      @select,
      ' INTO VARIABLE @result ',
      ' DELIMITED BY ''^'' ',
      ' ROW DELIMITED BY ''],['' ',
      ' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF' );
   EXECUTE IMMEDIATE @unload;
   RETURN STRING ( '[', LEFT ( @result, LENGTH ( @result ) - 2 ) );    
END;

SELECT query_to_dataformat ( 'SELECT first_name, member_since, last_seen FROM Person' );

query_to_dataformat('SELECT first_name, member_since, last_seen FROM Person')
[Glenn^2009-11-10^2012-02-01],[Breck^2009-11-08^2012-01-31],[Volker^2009-11-11^2012-01-30]

[VolkerBarth · Accepted solution]

...and surely Breck has taken the time to blog about that solution in detail here ... and may not have taken the time to let the forum know:)

[VolkerBarth · Accepted solution]

In case there's a need to output the result set description (say, as "first row") as well (though that's not specified by @Ralph), I guess one could use the UNLOAD with the APPEND option to concatenate the description and the result set into one string...

[Former Member · Accepted solution]

This is fantastic, great solution! I think I stumbled upon the UNLOAD statement, but must have missed the fact it could be redirected to a variable instead of a file on disk. I am marking this solution as the "accepted" one because it appears to work for all the weird edge cases.