Search code examples
sqldatabasesap-aseflatten

How to merge columns of non unique rows in a database? (Sybase ASE)

Consider the data as:

|Column 1|Column 2|Column 3|
----------------------------
|A       |Tom     |1       |
|A       |Tom     |2       |
|B       |Ron     |3       |

There are few duplicates in Column 1 that are preventing me to create an index. I need to only create an index on Col 1.

How do I merge/flatten the values to get something like:

|Column 1|Column 2|Column 3|
----------------------------
|A       |Tom     |1,2     |
|B       |Ron     |3       |

How do we do this without using concatenate/LIST/STUFF? The database is Sybase ASE.

Solution

  • You'll have to write a loop to do this. But if you only want to create that index, why not create it as non-unique? If you have to create it as unique, just add an identity column to the table and create the index on column1 + the identity column (or use the auto-identity DBoption)