concatenate strings from a column into a single row in cognos

[hiranb]

Hi,
I have to  concatenate strings from a column into a single row

id    test  score  NEWDATAITEM
id1   A       1       121
id1   B       2       121
id1   C       1       121

Thanks for your help.

Hiran

[blom0344]

Bringing the test object will always generate 3 rows, no matter if you could concatenate the score

In SQL terms:

Code Select Expand


SELECT ID,(CAST(S1 AS VARCHAR(10))+(CAST(S2 AS VARCHAR(10))+(CAST(S3 AS VARCHAR(10))) AS STRING FROM
(SELECT ID,SUM(CASE WHEN TEST = 'A' THEN SCORE ELSE 0 END) AS S1,
SUM(CASE WHEN TEST = 'B' THEN SCORE ELSE 0 END) AS S2,
SUM(CASE WHEN TEST = 'C' THEN SCORE ELSE 0 END) AS S3
GROUP BY ID) TEMP