How can I merge multiple row data into one single row in COGNOS 10

Question

I'm trying to merge multiple rows into one for example I have one DIIN but the data could appear in one of four columns as per the picture. My aim is to try and have one row with the relevant information displaying in the one line.

My skills on COGNOS are limited so any reply description/pictures would need to be in billy basics form like a step by step guide or a link to any videos would be a bonus.

Thank you in advance.

Answer 1

If only one of the columns will have the "data", you can use the MAXIMUM detail aggregation for each of those columns.

Your graphic appears to show a bunch columns not relevant to the question. I assume what really matters is...

DIIN	External Due In	Internal Due Out	Internal Due In	External Due Out
00419710301		Due Qty 3 OU-Other Unit Committed Stock
00419710301			Due Qty 3 IU-AinU Exchange

...which would be represented in SQL in a format useful for those who would provide an an answer. It's helpful to use a tool like https://dbfiddle.uk/ for this.

In future, please consider entering data as text or SQL statements so that potential helpers don't have to do the heavy lifting for you.

In SQL terms (much easier to type than Cognos GUI elements), I think the solution have is...

SELECT DISTINCT DIIN
, [External Due In]
, [Internal Due Out]
, [Internal Due In]
, [External Due Out]
FROM TableName

...and the solution you are looking for is...

SELECT DIIN
, MAX([External Due In]) AS 'External Due In'
, MAX([Internal Due Out]) AS 'Internal Due Out'
, MAX([Internal Due In]) AS 'Internal Due In'
, MAX([External Due Out]) AS 'External Due Out'
FROM TableName
GROUP BY DIIN

DIIN	External Due In	Internal Due Out	Internal Due In	External Due Out
00419710301		Due Qty 3 OU-Other Unit Committed Stock	Due Qty 3 IU-AinU Exchange