How I merge Multiple columns into one column??

[jsingh71]

I have following Table :

Mono

Color

Sipmlex

Duplex

1

2

3

4

5

6

7

8

9

10

11

12

Now I want to merge All 4 column into one column name ' Type ' and add one additional column name ' Value ' where I show the sum like following:

Type               Value

-------             ------------

Mono            15

Color             18

Simplex         21

Duplex          24

How I got this??

[Anonymous]

you can try something like this..

Table:
Load
'Mono' as Type,
Mono as Value
From $(FileName);
Concatenate (Table)
LOAD
'Color' as Type,
Color as Value
From $(FileName);
Concatenate (Table)
LOAD
'Sipmlex' as Type,
Sipmlex as Value
From $(FileName);
Concatenate (Table)
LOAD
'Duplex' as Type,
Duplex as Value
From $(FileName);

hope that helps..

[kruppas78 · Accepted solution]

Hi,

so you can get this table:

Basis:

LOAD * INLINE [

    Mono, Color, Sipmlex, Duplex

    1, 2, 3, 4

    5, 6, 7, 8

    9, 10, 11, 12

];

Table:

Load

'Mono' as Type,

SUM(Mono) as Value

RESIDENT Basis

GROUP BY 'Mono';

LOAD

'Color' as Type,

SUM(Color) as Value

RESIDENT Basis

GROUP BY 'Color';

LOAD

'Sipmlex' as Type,

SUM(Sipmlex) as Value

RESIDENT Basis

GROUP BY 'Sipmlex';

LOAD

'Duplex' as Type,

SUM(Duplex) as Value

RESIDENT Basis

GROUP BY 'Duplex';

[#4]

Hi Jai,

The above is one way to do it, however if you have many values in 'Type' or if they vary, you code may need a lot of manual intervention.

I would rather use Crosstable to solve this. Please find an example file attached. Hope it works for you

Regards

RL

[jsingh71]

Thanx a lot lakhina.

[jsingh71]

Thank you very much Aadil.

[jsingh71]

Thanx Stefan its working.