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.
| Author |
Topic |
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
 Posted - 2005-02-15 : 13:21:33
|
| Esteemed friends, Is there a way to write a Query to produce something like the following?My table looks like this (sort of).Customer,Event,Error,Error_DescI need a query to produce something like this:Event_one | 19 Error_one | 4 error_desc1 | 1 error_desc2 | 2 error_desc3 | 1 Error_two | 6 error_desc1 | 1 error_desc2 | 4 error_desc3 | 1 Error_three | 7 error_desc1 | 1 error_desc2 | 5 error_desc3 | 1 Error_four | 2 error_desc1 | 1 error_desc2 | 1 Event_Two | Error_one | 1 error_desc3 | 1Event_Three |....you get the idea? I want to count by a nested grouping or something like that.What's the best way to do this?Thanks a million. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 14:10:23
|
| You can simply create an Access report to do the grouping as needed.- Jeff |
 |
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2005-02-15 : 14:36:27
|
| Ok, but there a SQL way? I may wind up converting my app to MSSQL or MYSQL down the road - I'd like to learn how to do this sort of thing in SQL alone. Thanks! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 15:03:50
|
| SQL isn't designed for presentation -- its job is to return raw, unformatted data in a simple, standard row/column format. The presentation layer -- in this case, Access's Report writer -- is what must do the indenting, grouping and formtating of the raw data.Once you move to MSSQL or MySQL, you find out that they don't HAVE a presentation layer! they are server applications. so you will use another product -- even Access -- to present the raw data returned from your SQL statements. SQL itself and databases in general are not designed to present data in a nice readable format -- they are designed to store, alter and retrieve data.This is a very, very important distinction to understand. Access blurs the line a bit, because it is not only a database, but also a presentation tool and often the functionality of the two is interwoven and hard to separate. But even within the framework of Access, there are database aspects vs. presentation aspects that you should be aware of.- Jeff |
|
|
|
JodyWood
Starting Member
3 Posts |
Posted - 2005-02-15 : 15:24:49
|
| SELECT Event, Error, Error_Desc, COUNT(1) As CntFROM tableGROUP BY Event, Error, Error_Desc |
|
|
|
sanu_s
Starting Member
2 Posts |
Posted - 2005-02-16 : 12:26:50
|
| Thanks a million.'Can you elaborate about the table structure and the way you want to retrive the data.According to my understanding you need to convert columns into rows that is pivote table. |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2005-02-19 : 15:32:10
|
| Thanks for your help. I was used to working with SQLserver and MYsql in the past, but never had to do any sort of presentation data.. I could do that in php or whatever.. now I have to use access and, your right,its a whole different animal. Anyhow, the solution I arrived at, that was actually clean and easy was a simple select query like posted above, populating and excel pivot table... looks perfect, and will work down the road too. Thanks again. all of you. |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2005-02-19 : 15:35:52
|
| PS. JodyWood! That was excellent. Perfect. Thanks. |
|
|
|
|
|
|
|
|
counts by nested groupings