Move columns to rows (fields to records)

[Leonel]

Thank you for reading



I need to move some columns/fields in a record as different records as per the tables below. The value in the AccountX field is a currency value.

I'll appreciate your help

lg

Existing table

Record	Account1	Account2	Account3	Account4
Rec1	125	40	22	322
Rec2	300	2	444	75

Expected query result

Record	Account	$Value
Rec1	Account1	125
Rec1	Account2	40
Rec1	Account3	22
Rec1	Account4	322
Rec2	Account1	300
... and so on

[Micron]

You would use a UNION query.
SELECT YourTable.Record, Account1 FROM YourTable
UNION
SELECT YourTable.Record, Account2 FROM YourTable
UNION
SELECT YourTable.Record, Account3 FROM YourTable;

I didn't use any ORDER BY clause anywhere, nor do I know if you will get duplicate records without knowing more about the real data. You might need a distinct predicate on each of the individual select statements.

[Leonel]

Thank you! Very much!

I think your input is taking me in the right direction.

How do I get the name of the field (in the example [Existing table].[Account1]) as a value under a new field named [Account] in [Expected query result].[Account]?

Thank you again
lg

[Micron]

Now I'm beginning to wonder what's going on. Maybe normalization of a less than ideal data design? Sure I've never done this, but my guess is

SELECT YourTable.Record, Account1, "Account1" AS Account FROM YourTable
UNION
SELECT YourTable.Record, Account2, "Account2" AS Account FROM YourTable
etc.

[Leonel]

Thx a lot

I wish it were a normalized table. The source is an XL table they dumped on my inbox :-)

Best
lg

[Micron]

I take it that it worked as you wanted, then.
In that case, you're welcome.