Evil wicked bad crosstab

[kramers]

I'm having the most difficult time accomplishing a crosstab query. At the moment I have results coming back like this

ID# LastName FirstName Code Number
1 Smith Steve CF 3
1 Smith Steve CON 1
4 Jones Walter CON 2
4 Jones Walter REF 1

I would like to put them in a table so they come back like this.
CF CON REF
1-Smith, Steve 3 1 0
4-Jones, Walter 0 2 1

I'm going crazy.

 

[mariebmk]

Try something like this:

Select ID, lastName, firstName,
SUM (CASE Code WHEN 'CF' THEN 1 ELSE 0 END) AS 'column1',
SUM (CASE Code WHEN 'CON' THEN 1 ELSE 0 END) AS 'column2',
SUM (CASE Code WHEN 'REF' THEN 1 ELSE 0 END) AS 'column3'
FROM <yourTable>
GROUP BY ID, lastName, firstName


Hopefully that helps!

Beth

 

[mariebmk]

Actually the following one will have the column headings right:

Select ID, lastName, firstName,
SUM (CASE Code WHEN 'CF' THEN 1 ELSE 0 END) AS 'CF',
SUM (CASE Code WHEN 'CON' THEN 1 ELSE 0 END) AS 'CON',
SUM (CASE Code WHEN 'REF' THEN 1 ELSE 0 END) AS 'REF'
FROM <yourTable>
GROUP BY ID, lastName, firstName


Beth