Crosstab - column names

[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

 

[Guest]

How many times did you think you needed to post this question?

 

[jliu1971]

My apology for the repeated posts.

Being new, I have no explanation. I first clicked send at 1:30 EST
and Google told me the server was busy. As I waited, I saw the
mistake in the TRANSFORM clause. So I posted again with the
correction at 2:50 EST. By the time I logged off Google at 4:50 EST,
my post had not shown. Before I saw them for the first time this
morning, I had assumed that it didn't go through and that I would have
to send again. What a surprise!

I'll try to do better next time.

Karl, my issue is I don't know how many levels there will be so the
iif will not work for me. Thanks anyway.

Duane, your suggestion looks great. I'll chew on it for a little
more. One question: if I construct a string of "[value1] AS [name1],
[value2] AS [name2]..." and do a "SELECT " & mystring, is it a less
efficient way?


Thanks in advance,
Jenni

 

[Guest]

I'm not sure what you are asking. Where would you construct this string and
how would you apply it? Are you building the SQL for a querydef?