473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to Transpose a TWO column table into Many Columns?

Hi all!
I have a table named tblCustomers, with the following fields,
Expand|Select|Wrap|Line Numbers
  1. ProductsName  CustomerName   Qty
  2. Maize         Mpoto           2       
  3. Maize         Mwita           3
  4. Maize         Mporo           5       
  5. Maize         John            1
  6. Mpunga        Mwita           0
  7. Mpunga        Mpoto           8
  8. Mpunga        John            4
  9. Mpunga        Mporo           1        
  10. Cake          Mpoto           12
  11. Cake          Mporo           4
  12. Cake          Mwita           3
  13. Cake          John            0                         
  14.  
I want the data to be displayed as follows
Expand|Select|Wrap|Line Numbers
  1. CustomerName    Maize   Mpunga   Cake
  2.  Mpoto           2        8       12  
  3.  Mwita           3        0        4      
  4.  Mporo           5        1        3         
  5.  John            1        0        0
  6.  
It cant be done in Crosstab-Query because of number of Columns & Rows in CustomersTable and CustomersTable_Transposed being not consistent.
I have searched on various sources but couldnt find something similar to this situation
Please help!!!
Dec 21 '10 #1

✓ answered by MikeTheBike

Hi

I don't understand why a crosstab query will not do what you require, ie.

TRANSFORM Sum(tblCustomer.Qty) AS SumOfQty
SELECT tblCustomer.CustomerName
FROM tblCustomer
GROUP BY tblCustomer.CustomerName
PIVOT tblCustomer.ProductsName In ("Maize","Mpunga","Cake");

Also, your illustrated output data does not reflect the table data !?

MTB

2 1418
MikeTheBike
639 Expert 512MB
Hi

I don't understand why a crosstab query will not do what you require, ie.

TRANSFORM Sum(tblCustomer.Qty) AS SumOfQty
SELECT tblCustomer.CustomerName
FROM tblCustomer
GROUP BY tblCustomer.CustomerName
PIVOT tblCustomer.ProductsName In ("Maize","Mpunga","Cake");

Also, your illustrated output data does not reflect the table data !?

MTB
Dec 21 '10 #2
I didnt know how could this be possible, finally its.
Thanks all for your time!
Happy X-Mas and New year
Dec 21 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Peter Foti | last post by:
Hi all, I have a form that contains a 2 column table. In the left column is the description for a particular input (ie - "First Name", "Last Name", "Phone Number", etc.). In the right column...
0
by: andrewcw | last post by:
After I made a nice application with WINFORM I tried to apply much of the same code, but there are lost of differences. I load the grid colors, column width, column header from a xml data file....
2
by: Hymer | last post by:
Hello, I have a small two-column table with three rows. The first column has a logo and the second column has the name of the organization. The logo's in the first column are too high. That...
5
by: Hymer | last post by:
Hello, I have a small two-column table with three rows. The first column has a logo and the second column has the name of the organization. The logo's in the first column are too high. That...
10
by: ste | last post by:
Hi there, I'm trying to query a MySQL database (containing image data) and to output the results in a HTML table of 3 columns wide (and however many rows it takes) in order to create a basic...
1
by: Sharif Islam | last post by:
I am getting this error while uploading a text file in a table: Error at Source for Row number 7370 Too Many Columns Found in the current row;;non-whitespace characters were found after the last...
2
by: Ryan Liu | last post by:
Hi, If I have a very big view in database, it covers 15 tables, each table has 1000 columns. When I issue select * from view, the database will give error -- too many columns. Can I use a...
12
by: jenniferhelen | last post by:
I am working with a query that has 6 columns and 101 rows; I would like to transpose the rows and columns. I have tried using a crosstab query but Access limits the row "fields" to 3 and this was...
3
by: jaimate | last post by:
I have a huge table with 10m entries. But there is only 500,000 different. The only way to identify each different entry is concatenating many columns. The final result is a 20 characters long, which...
4
by: luciegiles | last post by:
Hi, I wish to transpose data in an Access table currently in a single column over many rows to many columns over single row: Current format: CHI DateIssued 123456 01/01/10...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.