Converting rows into multiple columns

[farheenayesha]

I have a question as below,

i want to convert the above rows into columns as in below diagram.

please someone help me to get the solution.

Thanks in advance.

[flipside · Accepted solution]

If I'm right in assuming you will have a long list of records (6 rows for each record), then an approach like the following will achieve what you want ...

Data:

LOAD rowno() as link, B as [Cust Id]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'Cust Id';

left join LOAD rowno() as link, B as [first name]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'first name';

left join LOAD rowno() as link, B as [last name]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'last name';

.. + 3 more for age, gender, phone

This will only work if you can guarantee 6 pieces of data for each record AND all data labels are consistently named otherwise the link will corrupt.

flipside

[farheenayesha]

Thank you so much...this is exactly what i wanted!!

[#4]

Afreen,

Assuming your data has similar format as the sample, you may split the table and join it by the rowno() function as Flipside suggested.

This will work with any number of rows.

You must however, ensure, that your code does not form synthetic keys (look for it in Help or Reference Manual, which could easily run you out of memory, especially for large datasets.

Regards,

-Khaled.

[farheenayesha]

Thanks Khaled..

I'l make sure that my code does not form any synthetic keys.

[jeroen_wagner]

I want just this, only in a loop creating the Columns dynamicly, as the amount of rows>>columns  can change in time.

Any ideas?

So really some kind of loop, depending on the number of items, generating the columns needed