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

Question

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!

david.beche12 · Answer

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 dont 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.CheersDavid

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded