cross tab query from a single table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

cross tab query from a single table

Author

Topic

netedk
Starting Member

11 Posts

Posted - 2003-02-25 : 13:54:23

I have the foll tables:

table name: options
option_id option_display
1 A
2 B
3 C
4 D
5 M
6 N
7 O
8 P

Table: field_values
fld_id option_id recipient_id
1 1 A1
2 6 A1
1 1 A2
2 5 A2
1 2 A3
2 8 A3
1 2 A4
2 8 A4
1 4 A5
2 8 A5
1 4 A6
2 7 A6

Now I want to display the table results in the foll. fashion:
A B C D
M 1 0 0 0
N 1 0 0 0
O 0 0 0 1
P 0 2 0 1

Explanation: the options table has form options and the field_values table has user submitted values from a form. now in the reporting table I want to cross tab with field_id = 1 as column headers and fld_id =2 as row headers.

I have tried a lot - but to no avail. Most articles on cross-tabs assume data to lie in diff. tables. But here data lies in the same table - so how do I go about it?? can this be done usingg T-SQL at all? any help will be greatly appreciated.

Thanks a lot,

DK

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-25 : 14:00:54

Do you have a real world example? I can't see the forest for the trees with this one...It doesn't seem like your sample data supports what you're doing..sorry wish I could help

Brett

8-)

netedk
Starting Member

11 Posts

Posted - 2003-02-25 : 17:50:44

Hi Brett,

Sorry I forgot a field in the first table. I am putting my table structures again here:

table: form_fields (this table stores the diff. fields in a form)
fld_id fld_name
1 what is your favorite color?
2 what is your favorite means of transportation?

table name: options (stores the predefined options for fields)
option_id option_display fld_id
1 red 1
2 blue 1
3 green 1
4 orange 1
5 car 2
6 boat 2
7 air 2
8 bus 2

Table: user_values (stores the actual user responses)
fld_id option_id respondent_id
1 1 A1
2 6 A1
1 1 A2
2 5 A2
1 2 A3
2 8 A3
1 2 A4
2 8 A4
1 4 A5
2 8 A5
1 4 A6
2 7 A6

Now I want to display the table results in the foll. cross-tabs fashion:

Red Blue Green Orange
Car 1 0 0 0
Boat 1 0 0 0
Air 0 0 0 1
Bus 0 2 0 1

Hope I was able to elaborate my problem much more clearly now.

Thanks for any guidance you can provide.

DK

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-25 : 18:06:54

This should help:

http://www.sqlteam.com/item.asp?ItemID=2955

You'd have to join options to user_values twice, once as a color alias and again as a transport alias. I tried figuring out the join syntax but I can't see it yet. If I figure it out I'll post it, but you can probably figure it out pretty quickly. Once you do that, it would be passed as the @select parameter to the stored procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources