Link to home
Start Free TrialLog in
Avatar of smc1234
smc1234

asked on

SQL Crosstab Query

How can I display data below in a crosstab query?

Current select query example data:

Route      Type      Type_ID
Route1      CT      3301
Route1      CT      3324
Route1      LT      4403
Route1      RF      3393
Route2      CT      3301
Route2      CT      3302
Route2      LT      4403
Route3      CT      3322
Route3      LT      4403
Route3      RF      3395

I want to display data as:

Type      Route1 Route2       Route3
CT       3301       3301       3322
CT       3324       3302       
LT       4403       4403       4403
RF       3393                      3395
SOLUTION
Avatar of sameer2010
sameer2010
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rshq
Hello,

Do you want to do this using the SQL query itself? Then above link by "rshq" will help you.

If you want to achieve this in report builder, then you can use a crosstab report to achieve this.

- Harish
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of smc1234
smc1234

ASKER

I forgot to mention that in “current select query example data” Type column is not an actual column. I am getting this column by using sql below
‘CT’ as Type, etc..
Answer 1 by: sameer2010 and answer 4 by: jogos returns data which I need (CT types has two rows) when assuming that Type column is an actual column in an sql table. However, in my case is not and I don’t know how to get around this error: Invalid column name ‘Type’.  Also, is there any way to auto generate Route1, Route2, and Route3 column instead hard code because in some scenarios I might have more than three Routes? Thanks in advance for your help.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial