Rows into columns

[markmanley]

I've got a table which contains rows of data (previously created on another system and imported). The rows contain data like:

Jan Chevy 310

Jan GM 125

Jan Audi 289

Feb Ford 122

Feb Saturn 301

Feb Honda 227

March BMW 341

March Porsche 298

March Mini 432

...

What I need to do is get this data into columns so that it's more spreadsheet like.

i.e.:

Jan Chevy 310 Feb Ford 122 March BMW 341

Jan GM 125 Feb Saturn 301 March Porsche 298

Jan Audi 289 Feb Honda 227 March Mini 432

I've created a secondary table (in the same database) with the fields for the (9) columns going horizontally. The challenge I'm having is getting data from the rows in one table to the columns in the other table.

Here's my pseudo-code(can't copy from Edit Script window[!]). Note that I'm creating 3 sets of columns (9 columns total) as I want to see Jan, Feb, Mar going across the page, with data listed below it. I feel like I'm making this overly-complicated, but I can't seem to find an easier way.

loop

CreateNewRecord in ColumnDB

if Month = "Jan"

Set Field [toDB::col1; fromDB:col1]

Set Field [toDB::col2; fromDB:col2]

Set Field [toDB::col3; fromDB:col3]

else if Month = "Feb"

Set Field [toDB::col4; fromDB:col1]

Set Field [toDB::col5; fromDB:col2]

Set Field [toDB::col6; fromDB:col3]

else if Month = "March"

Set Field [toDB::col7; fromDB:col1]

Set Field [toDB::col8; fromDB:col2]

Set Field [toDB::col9; fromDB:col3]

end if

go to Record/request/Page [Next]

exit loop if EOD

Loop

all help is appreciated! Thanks in advance

[markmanley]

I've got a table which contains rows of data (previously created on another system and imported). The rows contain data like:

Jan Chevy 310

Jan GM 125

Jan Audi 289

Feb Ford 122

Feb Saturn 301

Feb Honda 227

March BMW 341

March Porsche 298

March Mini 432

...

What I need to do is get this data into columns so that it's more spreadsheet like.

i.e.:

Jan Chevy 310 Feb Ford 122 March BMW 341

Jan GM 125 Feb Saturn 301 March Porsche 298

Jan Audi 289 Feb Honda 227 March Mini 432

I've created a secondary table (in the same database) with the fields for the (9) columns going horizontally. The challenge I'm having is getting data from the rows in one table to the columns in the other table.

Here's my pseudo-code(can't copy from Edit Script window[!]). Note that I'm creating 3 sets of columns (9 columns total) as I want to see Jan, Feb, Mar going across the page, with data listed below it. I feel like I'm making this overly-complicated, but I can't seem to find an easier way.

loop

CreateNewRecord in ColumnDB

if Month = "Jan"

Set Field [toDB::col1; fromDB:col1]

Set Field [toDB::col2; fromDB:col2]

Set Field [toDB::col3; fromDB:col3]

else if Month = "Feb"

Set Field [toDB::col4; fromDB:col1]

Set Field [toDB::col5; fromDB:col2]

Set Field [toDB::col6; fromDB:col3]

else if Month = "March"

Set Field [toDB::col7; fromDB:col1]

Set Field [toDB::col8; fromDB:col2]

Set Field [toDB::col9; fromDB:col3]

end if

go to Record/request/Page [Next]

exit loop if EOD

Loop

all help is appreciated! Thanks in advance

[markmanley]

I've got a table which contains rows of data (previously created on another system and imported). The rows contain data like:

Jan Chevy 310

Jan GM 125

Jan Audi 289

Feb Ford 122

Feb Saturn 301

Feb Honda 227

March BMW 341

March Porsche 298

March Mini 432

...

What I need to do is get this data into columns so that it's more spreadsheet like.

i.e.:

Jan Chevy 310 Feb Ford 122 March BMW 341

Jan GM 125 Feb Saturn 301 March Porsche 298

Jan Audi 289 Feb Honda 227 March Mini 432

I've created a secondary table (in the same database) with the fields for the (9) columns going horizontally. The challenge I'm having is getting data from the rows in one table to the columns in the other table.

Here's my pseudo-code(can't copy from Edit Script window[!]). Note that I'm creating 3 sets of columns (9 columns total) as I want to see Jan, Feb, Mar going across the page, with data listed below it. I feel like I'm making this overly-complicated, but I can't seem to find an easier way.

loop

CreateNewRecord in ColumnDB

if Month = "Jan"

Set Field [toDB::col1; fromDB:col1]

Set Field [toDB::col2; fromDB:col2]

Set Field [toDB::col3; fromDB:col3]

else if Month = "Feb"

Set Field [toDB::col4; fromDB:col1]

Set Field [toDB::col5; fromDB:col2]

Set Field [toDB::col6; fromDB:col3]

else if Month = "March"

Set Field [toDB::col7; fromDB:col1]

Set Field [toDB::col8; fromDB:col2]

Set Field [toDB::col9; fromDB:col3]

end if

go to Record/request/Page [Next]

exit loop if EOD

Loop

all help is appreciated! Thanks in advance

[Søren Dyhr]

Let it be said, this question belongs to "Layout" and not "General" but you might get closer by reading this recipee:

http://interact.uoregon.edu/techweb/FM7Crosstab.html

--sd

[Søren Dyhr]

Let it be said, this question belongs to "Layout" and not "General" but you might get closer by reading this recipee:

http://interact.uoregon.edu/techweb/FM7Crosstab.html

--sd

[Søren Dyhr]

Let it be said, this question belongs to "Layout" and not "General" but you might get closer by reading this recipee:

http://interact.uoregon.edu/techweb/FM7Crosstab.html

--sd

[Ocean West]

Why not clean it up first in excel then reimport it?

[Ocean West]

Why not clean it up first in excel then reimport it?

[Ocean West]

Why not clean it up first in excel then reimport it?

[markmanley]

S

[markmanley]

S

[markmanley]

S