SQL Server 2005 Cross Tab?

GoCubs
GoCubs used Ask the Experts™
on
Ok, we are just looking into a move to SQL Server 2005 (I know, a little late but...)

What I need to do is a simple cross tab query. Here is what I have:
A table with multiple columns including Sex
I need to take the query:
select Sex, COUNT(*) SexCount from MyTable group by Sex
The results are:
Sex       SexCount
Male      1011
Female  1055

What I need is the following:
Column1    Column2
Male          Female
1011         1055

I have labeled the columns Column1 and Column2 because I don't know what the column names may have to be. Everything I have seen turns Male/Female into the ColumnHeader. I have seen messy stuff involving creation of temp tables, etc. My thinking is that there must be something new in 2005 that allows this to be done easily.

Is there?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
SELECT SUM(CASE WHEN Sex = 'Male' THEN 1 ELSE 0 END) AS Male, SUM(CASE WHEN Sex = 'Female' THEN 1 ELSE 0 END) AS Female
FROM MyTable

Author

Commented:
I already had the ability to have the counts with the Sex as a column header but what I need is for Male and Female to be in a row, not a column header to clarify from my post when I wrote:
What I need is the following:
Column1    Column2
Male          Female
1011         1055

Note that there are generic column headings so you can think of it like this:
               Column1    Column2
Row1      Male          Female
Row2      1011         1055
Top Expert 2010
Commented:
SELECT 'Male' AS Male, 'Female' AS Female
UNION ALL
SELECT CONVERT(varchar, SUM(CASE WHEN Sex = 'Male' THEN 1 ELSE 0 END)) AS Male,
      CONVERT(varchar, SUM(CASE WHEN Sex = 'Female' THEN 1 ELSE 0 END)) AS Female
FROM MyTable

Author

Commented:
That is a very good solution and it works. Although I needed it for this specific example, I realize now that I need to ask a more generic question which I will do now. However this solution works like a charm for my example.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial