Rows to Column (Comma Seperated) by key

which version , edition , and os?

you basically need to write a function to do this...

the function would take the key and value as input and return a varchar(?) of the categories..


 

how do you decide which rows belong to the same categoray ?
according to the first character ?
what is the maximum rows in a category ?

Version: DB2 8, Express Edition, Windows

Yes, you are correct, the first column (Key) would determinte the rows that belong together, there is no preset maximum rows in a category, through i dont foresee exceeding 50, or even 25.

one way of "converting" rows to columns is a join operation,
but this is only good if you have a fixed number of rows in each category
since you don't you will have to write a  function to do that

Yeah, I was able to do it using a function, with CURSOR and FETCH LOOP, not sure if that is the most effecient way to do it, that is why i posted here to see if there is a DIRECT SQL COMMAND or a better approch rather than opening a cursor and looping through the result set.

Hi kumarjeevan,

This is possible with recursive SQL.  Though it does take a bit to get one's head around, it does wind up being a single SQL statement.

It looks something like this:

with t0 (key, category, code, v) as
(
  select key, category, code, ''
  from x where category like '%1'

  union all

  select x.key, x.category, x.code, x.category || ',' || y.category
  from x, t0 y
  where x.key = y.key
    and x.category <> y.category
)
select * from t0;


Give me a couple of minutes to work out the details.
Kent

do we assume that the combination Key , Category , Value  is unique on the source table...
if so then KDO's recorusion solution works...

Otherwise does the source table have a unique key ?
  (again kdo's solution will work)

if the rows however don't have unique keys , then initially  a "row identifier" needs to be assigned
so that the source rows can be "uniquely combined" rather than either producing a cross product or
duplication....)

hth  

Thanks so much for the replies, but i tried Kdo's solution, i couldn't get it to arrive at the desied result set that i was looking for, in the source table, a combination of key and category is unique, and all that i want is to get a column in which the categoy for a given key is listed as comma seperated values. Any help is greatly appreciated.
Thanks Again.

Here's a slightly modified version that prints only the desired lines.


with vlist (key, element, category, code, v ) as
(
  select key, 1, category, code, cast (category as varchar (100))
  from x
  where category like '%1'

  union all

  select y.key, y.element+1, x.category, x.code, y.v || ',' || x.category
  from x, vlist y
  where x.key = y.key
    and x.category > y.category
)
select * from vlist
where vlist.element = (select max(element) from vlist t1 where vlist.key = t1.key);



Kent

Thanks Kdo, i really appreicate your help ....
The problem is, i dont have an ID field, but assuming that i can concatenate Key + category to create the unique ID, i still want evrey line in the table listed as is, but an additional column which basically groups all categories and displays them as comma seperated values. The above solution is great for grouping records based on a column and deriving a CSV list. but in my case i want the result set to have every sing line as is, with the addtional groups CSV column.
Again thanks so much for your continuous help, and hopefully with your help i can solve this mystery...

Hi kumarjeevan,

The ID column isn't needed in this case.  I've got a habit of putting identity columns on tables so that I can be assured of a unique key.

As far as adding the comma separate value string, just join the result back to the original table.


with vlist (key, element, category, code, v ) as
(
  select key, 1, category, code, cast (category as varchar (100))
  from x
  where category like '%1'

  union all

  select y.key, y.element+1, x.category, x.code, y.v || ',' || x.category
  from x, vlist y
  where x.key = y.key
    and x.category > y.category
)
select x.*, vlist.v from vlist, x
where x.key = vlist.key
  and vlist.element = (select max(element) from vlist t1 where vlist.key = t1.key);


 ID     KEY     CATEGORY     CODE     V        
 -----  ------  -----------  -------  --------
 1      1       C1           10       C1,C2    
 2      1       C2           20       C1,C2    
 6      3       C1           20       C1,C2    
 7      3       C2           20       C1,C2    
 3      2       X1           10       X1,X2,X3
 5      2       X3           20       X1,X2,X3
 4      2       X2           10       X1,X2,X3



Good Luck,
Kent

This should be it, you are great, one final quetion (hopefully), if i add records with just alpha (Category = AA, BB ETC and not ending with the number one, then the query totally excludes those records, anyway to over come that ?

Duh, i was able to solve this by removing the where clause in your sub query, i will apply these to my actual tables tonight and update back... thank you so much Kdo you are a genius !!!

It would be awesome, if you could shine some light or point me to some documentation on how the Recursive query works ...

Well -- above average....   :)



Glad to help,
Kent

There is a ton of documentation on the web that describes recursive SQL.  Some pretty good, most pretty bad.  The query that brought the concept into focus for me is the IBM documentation that queries a table of airline schedules to show how to get from Chicago to other destinations and how the flight segments interact.  It's here:

    http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyrecursivequeries.htm


There's also a fairly good set of slides (overheads) here that walk through a different process:

    http://www.gse-nordic.org/Working%20Groups/NRTC/Conferences/2005/DB2%20Stream/s44


A couple of words of caution.  Recursive SQL really is recursive.  A minor slip can generate an infinite loop.  To work effectively with recursive SQL, you have to be comforable with recursion in general.  If you can't write (in your favorite language) an expression evaluator or code to walk a b-tree, you probably won't ever get comfortable with recursive SQL.


Good Luck,
Kent

Thanks so much for the links, they were very helpfull. I really appreciate your kind help.
I tested the query on my actual tables, it works great, however i did notice that there was an error SQL0347W "The recursive common table express vlist may contain an infinite loop". how do i get rid od this, cause the error state is cauing problems in the actual application.

I'm not sure that the warning goes away.

DB2 raises the warning when it encounters recursive SQL.  If the warning occurs in a stored procedure or function it's easy to handle and/or ignore.  If you're running the SQL in an interactive client then the client is handling it.  You might check to see if the client has a setting to ignore this warning.


Kent

Thanks for all the help Kdo, I think i should be able to work arround the warning. Just wanted to make sure that this would not cause any other issues later down the road when the code goes live .....