db2 - SQL using CASE in SELECT with GROUP BY. Need CASE-value but get row-value -

so basicially there 1 question , 1 problem:

1. question - when have 100 columns in table(and no key or uindex set) , want join or subselect table itself, have write out every column name?

2. problem - example below shows 1. question , actual sql-statement problem

example:

a.field1, (select case when b.field2 = 1 b.field3 else null table b a.* = b.*) casefield1 (select case when b.field2 = 2 b.field4 else null table b a.* = b.*) casefield2 table group a.field1 

the story is: if don't put case own select statement have put actual rowname group , group doesn't group null-value case actual value row. , because of have either join or subselect columns, since there no key , no uindex, or somehow find solution.

dbserver db2.

so describing words , no sql: have "order items" can divided "zd" , "ek" (1 = zd, 2 = ek) , can grouped "distributor". though "order items" can have 1 of 2 different "departements"(zd, ek), fields/rows "zd" , "ek" both filled. need grouping consider "departement" , if designated "departement" (zd or ek) changing, want new group created.

select (case when table.departement = 1 table.zd else null end) zd, (case when table.departement = 2 table.ek else null end) ek, table.distributor, sum(table.something) something, table group zd ek table.distributor table.departement 

this here worked in select , zd, ek in group by. problem was, if ek not designated departement, still opened new group if changed, because using real ek value , not null case, explaining top.

and here ladies , gentleman solution problem:

select (case when table.departement = 1 table.zd else null end) zd, (case when table.departement = 2 table.ek else null end) ek, table.distributor, sum(table.something) something, table group (case when table.departement = 1 table.zd else null end), (case when table.departement = 2 table.ek else null end), table.distributor, table.departement 

@t-clausen.dk: thank you!

@others: ...