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:
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?
Post a Comment