Few columns to Rows

[raadwiptec]

hi all

I have a data as below in my database table

Contract No | Name | Address | Code1 | Code2 | Code3 | Code4 |

1                          xxxdf     dfdff           A             B                                D

2                           yyysd   tyyhj          B             C               A              A

I need the output as

Contract No | Name | Address | Code | 

1                          xxxdf    dfdff           A

1                          xxxdf    dfdff           B

1                          xxxdf    dfdff           C

1                          xxxdf    dfdff           D

2                           yyysd   tyyhj          B 

2                           yyysd   tyyhj          C

2                           yyysd   tyyhj          A

2                           yyysd   tyyhj          D

please suggest

[SONIAGESIMDE]

You could use:

crosstable (codex,CODE,3)
LOAD [Contract No ],
[Name ],
[Address ],
[Code1],
[Code2],
[Code3],
[Code4]
FROM [C:\Users\Sonia\Desktop\PRU.xlsx] (ooxml, embedded labels, table is Hoja1);

[raadwiptec]

Hi Sonia

Do you know how to transpose multiple columns?

For Example : If have another 2 additional columns 

Risk   |  Allocation

1                    AA

11                  BB

22                  CC

33                  DD

[SONIAGESIMDE]

Hi,

Assuming that the table was like this:

ContractNo	Name	Address	Code1	Code2	Code3	Code4	Risk	Allocation
1	xxxdf	dfdff	A	B		D	1	AA
2	yyysd	tyyhj	B	C	A	A	11	BB

You could do the following:

crosstable (codex,CODE,3)
LOAD ContractNo,
Name,
Address,
Code1,
Code2,
Code3,
Code4
FROM [C:\Users\Sonia\Desktop\PRU.xlsx] (ooxml, embedded labels, table is Hoja1);

crosstable (risk,RISK,3)
LOAD ContractNo,
Name,
Address,
Risk
FROM
[C:\Users\Sonia\Desktop\PRU.xlsx]
(ooxml, embedded labels, table is Hoja1);

crosstable (Allocation,ALLOCATION,3)
LOAD ContractNo,
Name,
Address,
Allocation
FROM
[C:\Users\Sonia\Desktop\PRU.xlsx]
(ooxml, embedded labels, table is Hoja1);

And you would get:

ContractNo	Name	Address	CODE	RISK	ALLOCATION
1	xxxdf	dfdff	A	1	AA
1	xxxdf	dfdff	B	1	AA
1	xxxdf	dfdff	D	1	AA
2	yyysd	tyyhj	A	11	BB
2	yyysd	tyyhj	B	11	BB
2	yyysd	tyyhj	C	11	BB