how to transpose multiple columns into rows?

[pemt]

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

 

[pemt]

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[pemt]

Hi Karl,

thanks a lot!

pemt

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[pemt]

Hi Karl,

how to reverse Item and Num in the crosstab?
I switch the columns Item and Num, but it shows like this:
Num 1 2 3 1 2 3 1 2 3
Item A A A B B B C C C
Value 10 22 78 32 40 87 34 79 98

Thanks,

pemt

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.


:

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt

 

[KARL DEWEY]

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Num
FROM YourTable
GROUP BY Num
PIVOT Item;

The results should be like this --
A B C
1 10 32 34
2 22 40 76
3 78 87 98
4 .... ... ...

Then Paste Transpose in Excel.
--
Build a little, test a little.

Hi Karl,

how to reverse Item and Num in the crosstab?
I switch the columns Item and Num, but it shows like this:
Num 1 2 3 1 2 3 1 2 3
Item A A A B B B C C C
Value 10 22 78 32 40 87 34 79 98

Thanks,

pemt

I think the limit for crosstab is 255.
I would suggest batches but that is a whole lot of batches.

Reverse Item and Num in the crosstab and then in Excel paste Transpose.

--
Build a little, test a little.

Hi Karl,

thanks a lot.
It works well for "Num" with only 3 cells, but it doesn't work for "Num"
with over one thounsand cells. The error said "too many lines for crosstab".
For Excel 2007, there is over 60,000 columns. Maybe the crosstab doesn't have
so many columns?
How to fix it?
thanks again for your always help.

pemt3

:

Crosstab query --
TRANSFORM Sum([Values]) AS TotalOfValues
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT Num;

--
Build a little, test a little.


:

Hi,

I have a query to make a table like this:
Item Num Values
A 1 10
A 2 22
A 3 78
B 1 32
B 2 40
B 3 87
C 1 34
C 2 76
C 3 98
actually each "Item" has more than a thousand of "Num".
how to "transpose" this "column table" into "row table"?
Like:
1 2 3 4 ....
A 10 22 78 ....
B 32 40 87 ...
C 34 76 98 ...

Thanks!

pemt