Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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 |