Find the #of rec for each data - not just grp by

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

Transact-SQL (2000)

Find the #of rec for each data - not just grp by

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 15:55:03

I have a table T1 with 2 Fields F1 & F2


F1	|	F2
------------------
A	|	q		   
B	|	r
A	|	s	
C	|	t
D	|	u
B	|	v
A	|	w
A	|	x
B	|	y
D	|	z
C	|	p

I need to find the number of records for each distinct value of F1 and return as column names.

Edited:
If it is complicated to do for any # of different Data, let there are only 4 different Data itms as "A", "B", "C" & "D"

Expected output (out of above data):
---------------------------------------
A B C D <-- as Column Names (Headers)
4 3 2 2 <-- as Data return

Following will help you to create table / data - if you want to

Create Table T1 (F1 varchar(1), F2 varchar(1))
Go

Insert Into T1
Select 'A', 'q' Union All
Select 'B', 'r' Union All
Select 'A', 's' Union All
Select 'C', 't' Union All
Select 'D', 'u' Union All
Select 'B', 'v' Union All
Select 'A', 'w' Union All
Select 'A', 'x' Union All
Select 'B', 'y' Union All
Select 'D', 'z' Union All
Select 'C', 'p'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-01 : 16:18:54

#1 -- Why do you need the put the data into columns like that? why not leave it as rows, in a standard RDBMS format? Are you just analyzing this data, or do you need to present it somewhere? What tool are you using for presentation?

#2 -- You have 420 posts here at SQLTeam and you haven't heard of a cross-tab yet?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 16:34:10

#1 -- I need it for a report. My real data is very huge and real query is highly complicated than this. This is a part of the query, from which I can figure out the answer for the rest of my Qs.
Its not Just analyzing data. The report is "Office Web Component" ( A kind of Mini Excel like grid comes as a control for VB & ASP.Net) and that is to be filled with data coming by a query.
The Query is to be written in a field of a table and once the user needs to run the query, he picks the required query (from a list of queries, put the necessary parameters & click submit button. the return of query is displayed in the Excel-Like grid. All done in VB

#2-- 420 posts is nothing compared with 12600 as Forum's Max by Tara, urs is 4750 ...
I learned a LOT from this site and thats mainly by trying to answer somebody else's Question. So I have 420 so far as I have the thirst for learning. I knew about cross-tab but its mainly in Access and it didn't strike my small brain its useful.
Anyway Thank you Dr. Cross Join

Ur hint, triggered my brain --> Cross-Tab Thanks again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-01 : 16:47:30

The "Office Web Component" mini-Excel grid is also known as a PIVOT TABLE! It's primary purpose is to take summarized data in a standard RDBMS format and to *pivot* it. Do not try to do this in T-SQL, you are only overcomplicating things. Just return the data with a simple SELECT, grouped as much as possible in a standard way, and let the pivot table create the columns. It's very, very easy to do.

Please don't make things hard on yourself .. use the right tool for the job.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-01 : 16:52:50

quote:

420 posts is nothing compared with 12600 as Forum's Max by Tara, urs is 4750 ...

Please don't insult me when I'm not even involved in the conversation.

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-01 : 17:01:21

Tara,
Oh No, I never intended to insult you.
I just mentioned the fact that you are in the top of the list of highest number of posts.
Pardon me if I said something which hurt you.

jhermiz

3564 Posts

Posted - 2006-03-01 : 18:04:23

quote:
Originally posted by jsmith8858

#1 -- Why do you need the put the data into columns like that? why not leave it as rows, in a standard RDBMS format? Are you just analyzing this data, or do you need to present it somewhere? What tool are you using for presentation?

#2 -- You have 420 posts here at SQLTeam and you haven't heard of a cross-tab yet?

Just cause he has 420 posts does not mean he should know everything. I am tired of people thinking the number of posts should mean you are some sort of guru. What happens if all of those posts were in Yak Corral?

Sri, if you are using RS, the type of report you should be after is known as a Matrix report. This is analogous to a cross tab report in access.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-01 : 18:14:29

Jon, try reading my post again ... did I imply that I expected him to be a "guru"? Did you see the smiley face? Come on, you've been great lately, don't go starting things again.

By the way -- did you ever get that combo-box in the DataGrid thing to work?

jhermiz

3564 Posts

Posted - 2006-03-01 : 22:17:18

quote:
Originally posted by jsmith8858

Jon, try reading my post again ... did I imply that I expected him to be a "guru"? Did you see the smiley face? Come on, you've been great lately, don't go starting things again.

By the way -- did you ever get that combo-box in the DataGrid thing to work?

Not starting anything but just because he has that many posts doesnt mean we expect him to know all. Yes I reposted (I think) on that thread that I had completed that. Works a treat, and the performance is pretty quick. Like the idea of storing an invisible column with the previous value and checking if the new value <> old value. Saves a lot of time processing the data. I've seen some bad implementations out there that make a trip to the server per every row even if it was not updated.

Handy code, if anyone is interested in that thread I can post the entire solution, or specific portions!

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources