Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Monday, June 04, 2007

Rows to single string or aggregating strings

In oracle there is no build-in group function that will concatenate strings, just like max summarizes all numbers. But there is quite easy way to do that in pure SQL. Assume we want to have get as an query result list of tables and column. But we want all columns to be concatenated and comma separated. Result should look
TABLE_NAME COLUMN_LIST
BLOG_LABELS BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE CCH_URL, CCH_TIME, CCH_VALUE
Lets start with hierarchical query to build "tree" for each table. The only one branch of this tree will start with first column and end with last one. SYS_CONNECT_BY_PATH gives as option to track that branch:
SQL> select table_name, 
  2        SYS_CONNECT_BY_PATH(column_name,  ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  ;
TABLE_NAME COLUMN_LIST
BLOGS , BGH_ID
BLOGS , BGH_ID, BGH_NAME
BLOGS , BGH_ID, BGH_NAME, BGH_FEED_URL
... ...
Then I just take max path and truncating leading comma gives me expected result:
SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  group by table_name;
TABLE_NAME COLUMN_LIST
BLOG_LABELS BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE CCH_URL, CCH_TIME, CCH_VALUE
Function Max works becouse if one string (ex.: "abc") is an prefix of the second one (ex: "abc123") than this second one is threated as bigger. I must warn that it might be not true for all NLS settings.

But what if we want columns to be ordered alphabetically? Than we have to use Row_Number function to calculate column position in table and make same changes to SQL:
SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from (select table_name,  column_name,  ROW_NUMBER() OVER (partition by table_name ORDER BY column_name) AS curr  from user_tab_columns)
  4  start with curr = 1
  5  connect by table_name = prior table_name
  6             and curr = prior curr +1
  7  group by table_name;
TABLE_NAME COLUMN_LIST
BLOG_LABELS BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS BLC_AUTHOR, BLC_AUTHOR_URI, BLC_BLG_ID, BLC_CONTENT, BLC_IDENTIFIER, BLC_PUBLISHED, BLC_PUBLISHED_TEXT, BLC_UPDATED, BLC_UPDATED_TEXT, BLC_URL
BLOG_POSTS BLG_BGH_ID, BLG_CONTENT, BLG_ENTRY, BLG_ID, BLG_IDENTIFIER, BLG_PUBLISHED, BLG_PUBLISHED_TEXT, BLG_TITLE, BLG_UPDATED, BLG_UPDATED_TEXT, BLG_URL
BLOGS BGH_FEED_URL, BGH_ID, BGH_NAME
FEED_CACHE CCH_TIME, CCH_URL, CCH_VALUE
Hope you will find it useful and fun.
All samples where run on Oracle 10gR2 XE 10.2.0.1.
Schema used in this sample is described in Blogger Backup tool entry.

Paweł

3 comments:

Anonymous said...

Nice post... only downside with this approach is that if you have a large number of columns, you may end up with :

SQL> select table_name,
2 ltrim(max(SYS_CONNECT_BY_PATH(column_name, ',')), ',') column_list
3 from user_tab_columns
4 where table_name = 'LARGE_TABLE'
5 start with column_id = 1
6 connect by table_name = prior table_name
7 and column_id = prior column_id +1
8 group by table_name
9 /
from user_tab_columns
*
ERROR at line 3:
ORA-01489: result of string concatenation is too long

Paweł Barut said...

Thats right. The limit for string length in SQL is 4000 characters.

Anonymous said...
This comment has been removed by a blog administrator.
 

Copyright © Paweł Barut
Printing from DOS to USB Printer