Wednesday, July 17, 2013

Example: SELECT LIST() FROM sa_split_list()

You might not code SELECT LIST() together with FROM sa_split_list() in the same query, but you might use them both on the same data.

The SQL Anywhere LIST() aggregate function turns a result set into a single string, and the sa_split_list() system procedure turns a string into a result set. Here's a round-trip example; first, the data:

CREATE TABLE t (
   id   DECIMAL ( 2 ) NOT NULL PRIMARY KEY,
   name VARCHAR ( 2 ) NOT NULL );

INSERT t VALUES ( 1, 'NY' ), ( 2, 'NJ' ), ( 3, 'VA' ), ( 4, 'DC' ), ( 5, 'CA' );
COMMIT;
 
SELECT * 
  FROM t
 ORDER BY id;

  id name 
---- ---- 
   1 NY   
   2 NJ   
   3 VA   
   4 DC   
   5 CA   
No, the fancy INSERT statement isn't using either LIST() or sa_split_list(), it's showing off the relatively new multiple row syntax of the VALUES list, aka the row constructor syntax... no Help link for this because, well, the Help is no help on this particular topic...

... ok, ok, see for yourself :)

Here's how LIST() works to create simple id and name strings, with the name string ordered by the corresponding id rather than alphabetically:
BEGIN
DECLARE @ids   VARCHAR ( 10 );
DECLARE @names VARCHAR ( 20 );

SELECT LIST ( STRING ( id ) ORDER BY id ) 
  INTO @ids
  FROM t;

SELECT LIST ( STRING ( name ) ORDER BY id )
  INTO @names
  FROM t;

SELECT @ids, @names;
END;

@ids       @names               
---------- -------------------- 
1,2,3,4,5  NY,NJ,VA,DC,CA       
So far, so good; here's the other way, using sa_split_list() to turn the strings into single-column result sets:
BEGIN
DECLARE @ids   VARCHAR ( 10 );
DECLARE @names VARCHAR ( 20 );

SET @ids   = '1,2,3,4,5';
SET @names = 'NY,NJ,VA,DC,CA';

SELECT row_value AS id
  FROM sa_split_list ( @ids, ',' )
 ORDER BY line_num;

SELECT row_value AS name
  FROM sa_split_list ( @names, ',' )
 ORDER BY line_num;
END;

id
--
1
2
3
4
5

name
----
NY
NJ
VA
DC
CA
Now, how about combining both strings to recreate the original table in one SELECT? The following code depends on the fact the two strings are positional; i.e., they both have the same number of entries, and each entry in one string corresponds to the entry in the same position in the other string:
BEGIN
DECLARE @ids   VARCHAR ( 10 );
DECLARE @names VARCHAR ( 20 );

SET @ids   = '1,2,3,4,5';
SET @names = 'NY,NJ,VA,DC,CA';

SELECT tid.row_value   AS id,
       tname.row_value AS name
  FROM ( SELECT * FROM sa_split_list ( @ids, ',' ) )   AS tid
       INNER JOIN 
       ( SELECT * FROM sa_split_list ( @names, ',' ) ) AS tname
       ON tid.line_num = tname.line_num
 ORDER BY tid.line_num;
END;

id   name 
---- ---- 
1    NY   
2    NJ   
3    VA   
4    DC   
5    CA   


1 comment:

Unknown said...

I really like the combo list and sa_split_list!
And find your blog as the best resource on SQL Anywhere:)

We have some cases where we have as comma separated string of id's and would like to get the corresponding rows, like:
SELECT * FROM t WHRE id IN (SELECT row_value FROM sa_split_list(@list_of_ids, ','))
and it works fine, but...
Would love to be able to make a more intuitive function/procedure that would allow me to write the query like:
SELECT * FROM t WHRE id IN Split(@list_of_ids)
Where Split would take an optional second parameter: Delimiter

Have not been able to make such procedure, any hints?