help me about format data - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

help me about format data

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-05 : 05:07:26

VID Question Answer
2 ---- A ------A10
2 ---- B ------A20
2 ---- C ------A38
2 -----D-------A40

4 --- A -------A10
4 --- B -------A22
4 --- C -------A37
4 --- D -------A42

5 --- A -------A10
5 --- B -------A22
5 --- C -------A39
5 --- D -------A42

8 --- A -------A11
8 --- B -------A22
8 --- C -------A36
8 --- D -------A43
......

A : (From A10 to A19) B: (From A20 to A29)
C : (From A30 to A39) D:(From A40 to A49)

Now i want to export format :

Question is A and answer is A10 - 3 times
Question is A and answer is A11 - 1 times
......
Question is B and answer is A20 - 1 times
Question is B and answer is A22 - 3 times
..........

Question is C and answer is A36- 1 times
Question is C and answer is A37- 1 times
Question is C and answer is A38- 1 times
Question is C and answer is A39- 1 times

Any one help me ?
Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-05 : 05:22:47

select Question, Answer, count(*) as times
from table
group by Question, Answer
order by Question, Answer

No, you're never too old to Yak'n'Roll if you're too young to die.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-05 : 11:48:03

quote:
Originally posted by webfred

select Question, Answer, count(*) as times
from table
group by Question, Answer
order by Question, Answer

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you very much.

I want to ask more ?
how to get sub select from above result of your statement,such as i want to order times field ....so on...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-05 : 12:52:58

Can you give some example data and table structure?
Because it is not easy without knowing your data and table structure.

No, you're never too old to Yak'n'Roll if you're too young to die.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-05 : 21:51:31

quote:
Originally posted by webfred

Can you give some example data and table structure?
Because it is not easy without knowing your data and table structure.

No, you're never too old to Yak'n'Roll if you're too young to die.

If I follow of your statement :
Question Answer Times
--------------- 3
--------------- 9
--------------- 4
--------------- 1
I need to order by times :
Question Answer Times
--------------- 1
--------------- 3
--------------- 4
--------------- 9
Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-05 : 22:14:25

change the order by statement to

ORDER BY times

KH
[spoiler]Time is always against us[/spoiler]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-06 : 03:16:08

quote:
Originally posted by khtan

change the order by statement to

ORDER BY times

KH
[spoiler]Time is always against us[/spoiler]

Sorry sir,
I change :
select Question, Answer, count(*) as times
from table group by Question, Answer order by Question, Answer,Times

It's not affect.Result is not change.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 04:11:02

KH meant:
order by ~~Question, Answer,~~ Times

No, you're never too old to Yak'n'Roll if you're too young to die.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-06 : 04:58:16

quote:
Originally posted by webfred

KH meant:
order by ~~Question, Answer,~~ Times

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks but i when i do that,result is not my wish
Question Times
A------- 1
B------- 2
A------- 3
C-------- 4
A--------22

I need the folowing format :
A -----1
A -----3
A -----22
B-----2
C-----4
Question need to group and times need to order.
Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 05:05:00

order by Question, Times

No, you're never too old to Yak'n'Roll if you're too young to die.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-01-06 : 22:18:52