Crosstab - column names

J

jliu1971

Hi,

I have a query that looks like this:

TRANSFORM mytable.var1,Count(mytable.var2) AS cnt
SELECT mytable.var1, count(mytable.var2) AS TOTAL
FROM mytable
GROUP BY mytable.var1
PIVOT mytable.var2;

and my result looks like this:

var1 female male
1 30 10
2 40 20
3 50 30

Is there a way I can name the columns to "column1", "column2" and so
forth, instead of female and male? Assume there are any number of
columns.

Thanks in advance,
Jenni
 
K

KARL DEWEY

You have error in your query ---
TRANSFORM Sum(mytable.var1) AS SumOfvar1
SELECT mytable.var1, Count(mytable.var2) AS TOTAL
FROM mytable
GROUP BY mytable.var1
PIVOT mytable.var2;

What other column name would you use for gender?
Hermaphrodite/genderless/sexless/unknown
You can use IIF statements to change the mytable.var2 to other text.
TRANSFORM Sum(mytable.var1) AS SumOfvar1
SELECT mytable.var1, Count(mytable.var2) AS TOTAL
FROM mytable
GROUP BY mytable.var1
PIVOT IIf([var2]="female","column1","column2");
 
Top