asked on

cross tab query

I have a table named playerredemptiomn that has 2 columns

Playerid, redemptiondescription

There are like 10 values for redemption description

I am trying to write a query that will list player id, all the values for redemption description and the number of occurrences for each one

here is some sample data

playerid redemptiondescription
12345 deli
12345 buffet
12345 buffet
12341 deli

the query should return

playerid deli buffet
12345 1 2
12341 1 0

Please help

ButlerTechnology

Try the following code:

 Select *
from playerredemptiomn 
pivot (count(redemptiondescription) 
for redemptiondescriptionin ([Deli], [Buffet])) as pvt

Open in new window

The downside is that you need to specify all the potential columns.

Tom

johnnyg123

ASKER

I thought I had seen something that would avoid having to list the potential values because they will change from time to time

ButlerTechnology

I am not sure of a way to get around of not having to list the columns except by shifting the solution to a stored procedure.
Tom

johnnyg123

ASKER

ok..if I shift to stored proc how could I do it?

ButlerTechnology

Before moving to store procedure solution, what technology will you be using to ultimately display the table? (SQL Select, Web Page, Excel, Access) I ask the question as perhaps the solution for creating the cross-tab lies within that application that is doing the final output.

Tom

johnnyg123

ASKER

just a one time ad hoc query that I was going to paste results into excel

ASKER CERTIFIED SOLUTION

ButlerTechnology

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial