Concatenate multiple columns into one row...

Toad Data Point & Toad Intelligence Central
1

I am currently using Toad Data Point 3.2 and I am connected to a Sybase IQ reporting table, my issue is I’m trying to concatenate multiple columns of data into one row. I’ve looked around many sites and have tried different techniques with no success. Here is a break down of what I need…

Temp table = [tag:finaltbl]

The table mentioned above contains data with these columns (record_ID, seq_Number, txt_Notes)

The data is as follows:

record_ID seq_Number txt_Notes

111 0 this is some text that

111 1 continues on the next sequence

111 2 number and would like

111 3 to combine into one row.

The above table results I would like to view is:

record_ID txt_Notes

111 this is some text that continues on the next sequence number and would like to combine into one row.

I’ve tried using XML Path (doesn’t work), Concat (doesn’t work) and a bunch of other methods but they don’t seem to either work or produce the end results.

Hopefully someone here and point me into something that can get me what I need, I appreciate any help and thank you in advance!

2

I think you want group_concat(). However in Sybase you may have to use a stored proceedure: stackoverflow.com/…/mysql-group-concat-equivalent-in-sybase-ase.

You could import to MySQL and perform the group_concat()

3

One thing you can do (not the most elegant) is to find the Max squence number for the set of data you are going to pull. Then left join to the same table (with alias names) that number of times.

Select Tbl1.Record_ID, Tbl1.txt_Notes + Tbl2.txt_Notes + Tbl3.txt_Notes + Tbl4.txt_Notes + … as Notes

From #finaltbl Tbl1

Left Join #finaltbl Tbl2

On Tbl2.record_ID = Tbl1.record_ID

And Tbl1.seq_Number = 0

And Tbl2.seq_Number = 1

Left Join #finaltbl Tbl3

On Tbl3.record_ID = Tbl1.record_ID

And Tbl1.seq_Number = 0

And Tbl3.seq_Number = 2

Left Join #finaltbl Tbl4

On Tbl4.record_ID = Tbl1.record_ID

And Tbl1.seq_Number = 0

And Tbl4.seq_Number = 3

Left Join …