How can I create a self join on a column in an OLAP table?

Hi Team,

I have the following situation:

1 column reflects product IDs and the other column are attributes of the product like color, size, etc.

Example:
I have the following table:

Ids Attributes
1 Red
1 5
1 3
2 Blue
2 3
2 1

I have created new columns for each attribute but the result looks like this:

Ids Color Size Pieces
1 Red - -
1 - 5 -
1 - - 3
2 Blue - -
2 - 3 -
2 - - 1

I need to transform this table in the following format:

Ids Color Size Pieces
1 Red 5 3
2 Blue 3 1

I am not sure if a self-join on the attributes column and based on the Product IDs is the right solution here. If yes does anyone know how to do it?

If not then is there a better solution?

Thanks for the feedback!

Hi Patrick,

if you have the second table you described (the one with one column per attribute), you can simply use the MIN or MAX aggregation functions on the attributes to get the desired result.

However, it is not clear to me how you get the second table from the first one. Did you do this in the transformation? If yes, why don’t you create the desired table in the transformations in the first place?

An alternative could also be the PU_STRING_AGG function. You can use this on the first table to aggregate all attributes per ID to one string. However, in that case, you will not have one column for each attribute in your result, but one string value containing all the attributes.

Cheers
David