Transpose Rows in Columns

[Former Member]

Hey.

I need to transpose a Table.

That's how it looks like:

TABLE:

OBJEK     ATINN     ATWRT

1                   a               5

1                    b               8

1                    c               6

2                    b               4

2                    c                9

I want to have following structure:

TABLE2:

OBJEK     ATWRTa     ATWRTb     ATWRTc

1                    5               8               6

2                    ?               4               9

I thougt of following query:

select "OBJEK",

  (case when "ATINN" = 'a' then "ATWRT"  end) as "ATWRTa",

(case when "ATINN" = 'b' then "ATWRT" end) as "ATWRTb",

  (case when "ATINN" = 'c' then "ATWRT"  end) as "ATWRTc"

from

  "P1591085134"."AUSP1"

group by "OBJEK"

But the compiler tells me that "ATINN" is not a group by expression...

Without the group by command i get the following result:

OBJEK     ATWRTa     ATWRTb     ATWRTc

1                    5               ?               ?

1                    ?               8               ?

1                    ?               ?               6

2                    ?               4               ?

2                    ?               ?               9

Is there another possibility? Probably with a stored procedure?

[Former Member]

Hi Vitalij,

There may be a more elegant solution, however you statement above will work if you insert a sum around each aggregated figure. Try the following:

select "OBJEK",

  sum(case when "ATINN" = 'a' then "ATWRT"  end) as "ATWRTa",

sum(case when "ATINN" = 'b' then "ATWRT" end) as "ATWRTb",

  sum(case when "ATINN" = 'c' then "ATWRT"  end) as "ATWRTc"

from

  "P1591085134"."AUSP1"

group by "OBJEK"

If a field is not in the group by, then some type of aggregation should be applied.

Regards,

Gary

[Former Member]

actually my example data wasn't that good. ATWRT ist not only numeric. There are also normal words. So I can#T du a sum(). Is there another opportunity?

[Former Member]

OK, this will depend upon your data, however, if you are sure that there is a single value for each combination of OBJEK and ATINN, then you could use the following aggregate function which works for characters as well:

select "OBJEK",

          min(case when "ATINN" = 'a' then "ATWRT"  end) as "ATWRTa",

          min(case when "ATINN" = 'b' then "ATWRT" end) as "ATWRTb",

          min(case when "ATINN" = 'c' then "ATWRT"  end) as "ATWRTc"

from  "P1591085134"."AUSP1"

group by "OBJEK"

I tried this in my system with an additional row to your list for the following:

OBJEK     ATINN     ATWRT

2              a            a

The above SQL gives me the following results

OBJEK          ATWRTa          ATWRTb          ATWRTc

    1                    5                    8                     6

    2                    a                    4                     9

Again, you really need to be sure that there is only 1 value for each combination of OBJEK and ATINN, otherwise this would not work.

Regards,

Gary

[Former Member]

That's very helpful! I'm sure that there is only 1 value for each combination of OBJEK and ATINN.

Thank you very much!

[Former Member]

Hi Gary,

This logic works fine. Can you please suggest if I need to make this work for dynamic set of records how can I implement this?

regards,

Sridhar

[former_member184969]

Thanks Shrigar for raising that problem.

It seems by the name of your field we share the same objective , which is more or less to dynamicaly denormalize classification table to unable analysis of Material-related facts by characteristic value (or so I guess).

Justins'comment, in Shosh'n note , is giving som tracks to follow.

I was just wondering if you foun a solution in the last months ?

Thanks

Regards

Stephane