Question about creating columns from data in rows

[Sol]

If I have data in the following format in a database

LE Amount BS Class
1 10 Asset
1 15 Liability
1 20 Asset
1 12 Liability
2 45 Asset
2 74 Asset
2 12 Liability
I want to extract the sum of each LE by BS Class, but I want the BS
class in two seperate columns.

So
LE Assets Liablities
1 30 27
2 119 12
What SQL code can I use to do that.

NB I have run two seperate queries to return Assets & liabilities, but
if one LE has no assets the two tables get out of sequence. I really
need it to say 0 assets vs the liabilities.

[Rich P]

Hi Sol,

If your data is in the format you describe then you can use a crossTab
query to get the results you desire. Just click on New Query and select
Crosstab from the list. A wizard will come up to guide you through the
query creation process. Select LE for Row Heading, select BS Class for
Column Heading, and select Sum for Amount. You may want to uncheck the
"Include Sum Total" in the lower left corner of the dialog that contains
the list with the Sum function.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Use a crosstab query (Crosstab on Query menu, in query design.)

LE is your Row Heading
BS Class is your Column Heading.
Amount is your Value, and it looks like you want Sum in the Total row.

Once you get the basics of crosstab queries down, here's some more involved
techniques:
http://allenbrowne.com/ser-67.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sol" <so***********@citi.comwrote in message
news:11**********************@r29g2000hsg.googlegr oups.com...

If I have data in the following format in a database

LE Amount BS Class
1 10 Asset
1 15 Liability
1 20 Asset
1 12 Liability
2 45 Asset
2 74 Asset
2 12 Liability
I want to extract the sum of each LE by BS Class, but I want the BS
class in two seperate columns.

So
LE Assets Liablities
1 30 27
2 119 12
What SQL code can I use to do that.

NB I have run two seperate queries to return Assets & liabilities, but
if one LE has no assets the two tables get out of sequence. I really
need it to say 0 assets vs the liabilities.