Okay, I'm struggling here. I'm sure this can be done but I can't see the wood for the trees...

The table contains data like...

1 A
1 B
1 C
2 A
2 B
2 C

And I want to convert that to...

1 A,B,C
2 A,B,C

I can't do this in a procedure or a function, it has to be a standard SQL statement. Any ideas?

You can check out this site for a possible answer:

http://searchdatabase.techtarget.com...285649,00.html

I just did this (like yesterday) by writing a SQL function to perform this task. If you want the code let me know.

Andy

Thanks Andy.

Unfortunately, I will probably have a large (and varying) number of elements to concatanate, so that won't work for me.

After much sweat and several tears, I came up with this though (I've included some example data if you want to try it)...

CREATE TABLE STOCK_OPTIONS
(
STK_ID SMALLINT
, OPT_DESC CHAR(1)
);

INSERT INTO STOCK_OPTIONS
VALUES (1,'A'),(1,'B'),(1,'C'),(2,'A'),(2,'B'),(2,'C')
;

WITH INITIAL_STOCK_LIST ( STK_ID, OPT_DESC ) AS
(
SELECT DISTINCT STK_ID, RTRIM(OPT_DESC)
FROM STOCK_OPTIONS
)
-- The following temp table holds a count to be used to determine
-- which record will be the final record in the later, recursive select.
, STOCK_COUNTER (STK_ID, OPT_COUNT) AS
(
SELECT STK_ID, COUNT(*)
FROM INITIAL_STOCK_LIST
GROUP BY STK_ID
)
-- The following temp table adds an incremental index (opt_id)
, BASE_STOCK_LIST ( OPT_ID, STK_ID, OPT_DESC ) AS
(
SELECT COUNT(*), A.STK_ID, A.OPT_DESC
FROM INITIAL_STOCK_LIST A, INITIAL_STOCK_LIST B
-- This is the join used to attain an incremental index starting from 1
-- for the options
WHERE A.OPT_DESC >= B.OPT_DESC
AND A.STK_ID = B.STK_ID
GROUP BY A.STK_ID, A.OPT_DESC
)
, RECURSIVE_STOCK_LIST ( OPT_ID, STK_ID, OPT_DESC, COUNTER ) AS
(
SELECT OPT_ID, STK_ID, CAST(OPT_DESC AS VARCHAR(2000)), 1
FROM BASE_STOCK_LIST

UNION ALL

SELECT A.OPT_ID, A.STK_ID, A.OPT_DESC || ', '|| B.OPT_DESC, A.COUNTER + 1
FROM BASE_STOCK_LIST B, RECURSIVE_STOCK_LIST A
WHERE A.STK_ID = B.STK_ID
-- Only need to create one new row on each recursion
-- Use the counter to append the 'next' opt_desc element to the most
-- complete 'full' description ONLY
AND B.OPT_ID = A.OPT_ID + COUNTER
AND B.OPT_ID = COUNTER + 1

)
SELECT A.STK_ID, OPT_DESC
FROM RECURSIVE_STOCK_LIST A
, STOCK_COUNTER B
WHERE A.STK_ID = B.STK_ID
AND A.COUNTER = B.OPT_COUNT
;

I'm still working on the performance ;-)

Damian

IMHO, you can create BASE_STOCK_LIST using RANK rather than using a join ...

BASE_STOCK_LIST ( STK_ID, OPT_DESC,opt_id ) AS
(
select stk_id,opt_desc,
rank() over (partition by stk_id
order by opt_desc
)
from initial_stock_list
)

This will make it more readable and efficient

Cheers
Sathyaram

Brilliant!

While I'm at it... I've based all the query elements of the statement on the INITIAL_STOCK_LIST select, which is essentially a temporary table. Would I maybe see an improvement in performance if I used the original table data, which is indexed, rather than the temporary table data? It is possible/likely that the temporary data would be written to disk rather than be held in memory as the volume could be quite large.

That said, how could I determine what volume of data would require that a temporary table be written to disk?

Thanks, Damian