is the data resident in the same table or in different tables? if in the same table you might have to make use of self join which could potentially return more than what is expected. to get the results in concatenated form you can use col1 + " + " + col2 (if you need + as well need to include that in quotes). hope this helps.
i agree, how much levy do you have to split the data into multiple tables. is it possible? i tried most possibilities till now but couldn't find any suitable method as yet. Tried temp table, distinct one or the other leads to cartesian products which is not what we want. Writing a stored procedure is the only thing which i havent tried yet, maybe i would if i find time.
i thought the initial data was something of this format
x1 x2 x3
a 1 abc
b 1 def
c 2 bhj
a 1 xyz
b 2 d123
b 1 b123
so if we generate rownumber based on the above it would come out like
i.e. count of x1. Now with this approach the loop below won't satisfy your requirement i think. can you tell me how are you generating sample data set & correspondingly row number from data.
Since you're using rowid like operation have you taken into account holes in data (i mean if some of the rows are deleted, would the loop still work and give desired results). Honestly i can tell only if i know how the data is generated. But if it works for you then i don't see any problem.
Yeah you r right i am using rowid kind of functionality in sybase. Regarding holes in the data, this data will be build everyday morning (batch program).So, does not have to worry about deletion of data.
Also , i have tried this logic till 16 level and it is able to concatenate data till that level. So, i think its gonna work.
To generate rowid , i am using this query,
-------------------SQL Start ------------------
create table #b (a1 char(4), a2 varchar(10))
insert into #b values ("id","A")
insert into #b values ("id","B")
insert into #b values ("id","C")
insert into #b values ("id1","D")
insert into #b values ("id1","E")
insert into #b values ("id1","F")
insert into #b values ("id1","F1")
insert into #b values ("id2","G")
insert into #b values ("id2","H")
select a1,a2,rownumb =
(select count(*) from #b t2
--more joins can be added acc. to unique key, here col. a1 is a key
and t2.a2 <= t1.a2)
from #b t1
order by a1,a2
----------------- SQL end ---------------