Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 20th, 2004, 05:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Grouping

I need a consecutive line grouping algorithm. Lets say I have a database table that looks like this:
Code:
ID     Val1     Val2
1      A        10
2      B        20
3      B        30
4      C        40
5      B        50
6      B        60
I want to group it consecutively. When I group by Val1 using this statement:
Code:
select value1, sum(value2) from tbltestgroupings group by value1
I get:
Code:
ID     Val1     Val2
1      A        10
2      B        160
4      C        40
and what I want is:
Code:
ID     Val1     Val2
1      A        10
2      B        50
4      C        40
5      B        110
Does anyone know how I can accomplish this? This is very important and time critical, so if anyone has any help, it is appreciated.



Chris
__________________
Chris
  #2 (permalink)  
Old May 21st, 2004, 11:31 PM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

I dont understand u wanna grouping it & see ur B [u]twice</u>?! If u tell why u r gonna do that I may help u.

Always:),
Hovik Melkomian.
  #3 (permalink)  
Old May 24th, 2004, 11:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is a schedule of sorts. The schedule cannot change order (hence needing two B's), but I can group parts together when they occur together in sequence.

Chris
  #4 (permalink)  
Old May 24th, 2004, 11:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe the part where I said I "Can" group parts is not correct. I "have" to group them. I am replacing an existing system and this relly is just a chunk in the middle. I cannot change what comes in to me (As far as data) adn I cannot change how it goes on to the next process. Right now it is done with Excel and a bunch of complicated Macros and is relaly not a good setup. The reason the B is there twice is it just groups concurrent records of like data together and sums their values. Any help anyone could offer would be so greatfully received.

Chris
  #5 (permalink)  
Old May 24th, 2004, 11:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Chris, I'm not sure you can actually do what you're after using SQL (without some very dirty cheating). It might be that using Excel VBA (or another programming language) is the better method - the task seems more suited to a procedural function that a set-based one. To do this in SQL would essentially entail building a while-wend loop running off the ID.
How are you getting your raw data? Batch feed, line feed or something else entirely? We might be able to come up with a brilliant left-field idea if we know more about the process :) Perhaps if you are able to add a sequence indicator to the incoming data. i.e. something that increments each time Value1 changes. That way you would be able to add that to your group by clause and get what you want

Chris


There are two secrets to success in this world:
1. Never tell everything you know
  #6 (permalink)  
Old May 24th, 2004, 11:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The data comes to me in a SQL table. I merely need this grouping for showing the schedules on an intranet, and also for running queries against. The grouping is to get the data in abetter format for me to use with a much larger scale of SQL Server's capabilities.

Chris
  #7 (permalink)  
Old May 24th, 2004, 12:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, I'm still thinking that adding your own sequence indicator is the way to go, all we need to do is work out the best strategy for putting it there.
Do you get this table as a discrete object, or does it get continuously updated and you just query it as and when required? In the first instance, your best option is going to writing SQL looping code to add an indicator, in the second, you should be able to set up a trigger to do it.
The only other option I can think of probably involves correlated subqueries and similar high-order SQL magic ;) Where's Jeff Mason when you need him!

Chris

There are two secrets to success in this world:
1. Never tell everything you know
  #8 (permalink)  
Old May 25th, 2004, 05:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Chris,

Jeff is missing for a long time. I too was in search of him. And he posted one or two after that and again went on sleep mode. Hope he should be travelling. But how long? Come on Jeff, guys really need you at this time.

Cheers!

_________________________
-Vijay G
Strive for Perfection
  #9 (permalink)  
Old May 26th, 2004, 06:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sorry for my absence, guys.

I haven't been traveling, but rather we've been engaged with a new client who dares to place somewhat unreasonable demands on our time. Life has gotten a bit crazy right now due to the workload, so I've had very little time for participating in p2p. Sorry, but believe me, if you are going to have problems, too much work is the sort of problem to have, considering the alternatives. :D

I've taken a quick look at the OP's problem and no set based solution jumps out at me. If I understand the problem correctly, the requirement that the rows of the table be processed in ID order so that the desired "consecutive grouping" can be obtained precludes that. I'm not really sure what "consecutive grouping" is, but whatever it is, it's not a set operation...

I'm *very* suspicious of this (table) design...

I can't believe I'm suggesting this, but perhaps a cursor based solution would work. Process the rows one at time, summing the Val2 column until the Val1 column changes, insert the result into temporary table (or table variable if SQL2000), and SELECT the temporary to return the final resultset.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #10 (permalink)  
Old May 26th, 2004, 11:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe I can explain it better. Your solution may be my only option anyway. I work at a Steel Mill. These records are a list of products we are going to create. Each product may occur several times a year. At each interval of a p[roduct, we have several different lengths. At each interval where we create a product at a certain length, we may have several orders listed together. I need to group it at the order level. It will still need to be listed by interval and product and length. The report and functionality is built around product and the interval and length(which is why I need the product to be grouped by product and interval and length). The report has to stay in the same order (obviously). Thes data is in a table on another system (IBM AS400) and is then mirrored over to me. I have no control over the data, and I cannot change it. I could create another table or a temp table to join to it though.

Chris




Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect cells and allow grouping/un-grouping sfreuden Excel VBA 4 December 14th, 2006 08:01 AM
xsl grouping aiyer0912 XSLT 9 November 7th, 2006 12:13 PM
Grouping Indexes ShaileshShinde XSLT 3 November 30th, 2005 05:46 AM
Grouping the value in a variable d_sathish XSLT 2 November 8th, 2005 10:23 AM
grouping tools Cclhodges Beginning VB 6 2 June 2nd, 2004 10:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.