How do I concatenate strings from a column into a single row in DB2
in [DB2]
|
From: mark83anthony on 19 Nov 2008 14:28 How do I concatenate strings from a column into a single row? Whats the logic to create the function in DB2. Given is below. Color ------ red orange blue green And return a resultset like this: Colors ------------------------- red,orange,blue,green
From: mark83anthony on 19 Nov 2008 15:49 On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote: > How do I concatenate strings from a column into a single row? > > Whats the logic to create the function in DB2. > > Given is below. > > Color > ------ > red > orange > blue > green > > And return a resultset like this: > > Colors > ------------------------- > red,orange,blue,green I will make it clear. The table name is Colors CREATE TABLE Colors ( Color VARCHAR(32) ) Values in the Table are Color ------ red orange blue green My output should be when I do a Select * from Tabname, shd be red,orange,blue,green Waiting for your reply Thanks in Advancd
From: jefftyzzer on 19 Nov 2008 15:53 On Nov 19, 12:49 pm, mark83anth...(a)gmail.com wrote: > On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote: > > > > > How do I concatenate strings from a column into a single row? > > > Whats the logic to create the function in DB2. > > > Given is below. > > > Color > > ------ > > red > > orange > > blue > > green > > > And return a resultset like this: > > > Colors > > ------------------------- > > red,orange,blue,green > > I will make it clear. > > The table name is Colors > CREATE TABLE Colors > ( > Color VARCHAR(32) > ) > > Values in the Table are > Color > ------ > red > orange > blue > green > > My output should be when I do a Select * from Tabname, shd be > > red,orange,blue,green > > Waiting for your reply > > Thanks in Advancd Mark: Have a look at a posting to this newsgroup titled "concatenating historical records" from October '07. In it, I listed two ways I know of/have learned to do this. --Jeff
From: mark83anthony on 19 Nov 2008 17:22 On Nov 19, 3:53 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote: > On Nov 19, 12:49 pm, mark83anth...(a)gmail.com wrote: > > > > > > > On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote: > > > > How do I concatenate strings from a column into a single row? > > > > Whats the logic to create the function in DB2. > > > > Given is below. > > > > Color > > > ------ > > > red > > > orange > > > blue > > > green > > > > And return a resultset like this: > > > > Colors > > > ------------------------- > > > red,orange,blue,green > > > I will make it clear. > > > The table name is Colors > > CREATE TABLE Colors > > ( > > Color VARCHAR(32) > > ) > > > Values in the Table are > > Color > > ------ > > red > > orange > > blue > > green > > > My output should be when I do a Select * from Tabname, shd be > > > red,orange,blue,green > > > Waiting for your reply > > > Thanks in Advancd > > Mark: > > Have a look at a posting to this newsgroup titled "concatenating > historical records" from October '07. In it, I listed two ways I know > of/have learned to do this. > > --Jeff- Hide quoted text - > > - Show quoted text - Hi, I tried doing this SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a, Color))),'<A>',''),'</A>',' ') FROM Colors; It works fine with my DB2 9.5 client on my machine but does not work with Db2 Z OS 9.1. It gives me the following error message SQL0171N The data type, length or value of argument "1" of routine "REPLACE" is incorrect. SQLSTATE=42815 SQL0171N The data type, length or value of argument "1" of routine "REPLACE" is incorrect. Explanation: The data type, length or value of argument "<n>" of routine "<name>" is incorrect. The statement cannot be processed. User response: Ensure the arguments of the routine conform to the rules of the routine. sqlcode: -171 sqlstate: 42815
From: jefftyzzer on 19 Nov 2008 17:35 On Nov 19, 2:22 pm, mark83anth...(a)gmail.com wrote: > On Nov 19, 3:53 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote: > > > > > On Nov 19, 12:49 pm, mark83anth...(a)gmail.com wrote: > > > > On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote: > > > > > How do I concatenate strings from a column into a single row? > > > > > Whats the logic to create the function in DB2. > > > > > Given is below. > > > > > Color > > > > ------ > > > > red > > > > orange > > > > blue > > > > green > > > > > And return a resultset like this: > > > > > Colors > > > > ------------------------- > > > > red,orange,blue,green > > > > I will make it clear. > > > > The table name is Colors > > > CREATE TABLE Colors > > > ( > > > Color VARCHAR(32) > > > ) > > > > Values in the Table are > > > Color > > > ------ > > > red > > > orange > > > blue > > > green > > > > My output should be when I do a Select * from Tabname, shd be > > > > red,orange,blue,green > > > > Waiting for your reply > > > > Thanks in Advancd > > > Mark: > > > Have a look at a posting to this newsgroup titled "concatenating > > historical records" from October '07. In it, I listed two ways I know > > of/have learned to do this. > > > --Jeff- Hide quoted text - > > > - Show quoted text - > > Hi, > > I tried doing this > > SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a, > Color))),'<A>',''),'</A>',' ') > FROM Colors; > > It works fine with my DB2 9.5 client on my machine but does not work > with Db2 Z OS 9.1. > > It gives me the following error message > > SQL0171N The data type, length or value of argument "1" of routine > "REPLACE" > is incorrect. SQLSTATE=42815 > > SQL0171N The data type, length or value of argument "1" of routine > "REPLACE" is incorrect. > > Explanation: > > The data type, length or value of argument "<n>" of routine "<name>" > is > incorrect. > > The statement cannot be processed. > > User response: > > Ensure the arguments of the routine conform to the rules of the > routine. > > sqlcode: -171 > > sqlstate: 42815 Well, notwithstanding a simple fix for the problem you're having with the XML function, assuming that DB2 9.1 for Z/OS supports recursive common table expressions (CTEs), you could try the other technique demonstrated in the earlier posting I mentioned. --Jeff
|
Next
|
Last
Pages: 1 2 3 Prev: How to "LOAD COPY NO" without backup ? Next: System Time and Utilities Start/Stop Time |