Select Statement

Select Statement

Post by Pauline Ohania » Wed, 06 Oct 1999 04:00:00



Hi,
I've got a table that has records like this:

FieldID      FieldValue
----------      ---------------
    1                  Val1
    1                  Val2
    1                  Val3
    2                  Val4
    2                  Val5
    3                  Val6
    4                  Val7
    4                  Val8
etc...

I want to make a select statement on the above table that would give me
results like this:
FieldID      FieldValue
----------      ---------------
    1                  Val1, Val2, Val3
    2                  Val4, Val5
    3                  Val6
    4                  Val7, Val8

I want to group by FieldID and put commas in the FieldValues...

Is this possible with a select statement? If yes, anybody knows how to?

TIA,
Pauline

 
 
 

Select Statement

Post by Dejan Sark » Wed, 06 Oct 1999 04:00:00


Pauline,

I don't think it's possible in a single SELECT statement. You should use
WHILE statement or cursor. You can get in a single SELECT result like this:

FieldID      FieldValue
----------      ---------------
    1                  Val1
                        Val2
                        Val3
    2                  Val4
                        Val5
    3                  Val6
    4                  Val7
                        Val8

with statement like this:

SELECT FieldID=CASE
             WHEN t1a.col2=(SELECT MIN(t1b.col2) FROM table1 t1b
                             WHERE t1b.col1=t1a.col1)
                THEN t1a.col1
              ELSE ' '
             END,
        FieldValue=t1a.col2
 FROM table1 t1a
ORDER BY t1a.col1, t1a.col2

HTH,
Dejan Sarka