Wrox Programmer Forums
|
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
 
Old June 27th, 2003, 01:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default Odd Grouping Request

I have some data that I need to group I currently group on severla columns

Lets say I have the following fields and data

Item Grade Type Weight Sequence
1 A RR 11.2 1
1 A RR 11.2 2
2 B RR 12.7 3
1 A RR 11.2 4

I currently group on Item, Grade, Type and Weight and get this

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (3)
2 B RR 12.7 3 (1)

(I just take the minimum Sequence number on the group by - I added the Count field so you could see how many records were grouped there. There are many other columns also involved, but they are just totaled up so there is really a reason for me grouping these records.)

I somehow need to order this by the sequence and then group if they are next to each other only. This is what it would look like:

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (2)
2 B RR 12.7 3 (1)
1 A RR 11.2 4 (1)

Can anyone help me accomplish this? I am not sure how I would go about this. We have an existing report that runs like this (Old code off of an AS400 IBM OS) and I need to reporduce the report in ASP. I have the report basically done and I just now discovered this SQL problem. I am anxiously awaiting your replies.

Thanks in advance
__________________
Chris
 
Old June 30th, 2003, 07:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've been thinking about this problem a little and the best I have been able to come up with so far is this:

Code:
CREATE TABLE #Report
(
ReptID Int IDENTITY,
Grade char(1),
Type char(2),
Weight decimal(3,1),
Sequence Int
)

INSERT INTO #Report
(Grade, Type, Weight, Sequence)
SELECT Grade, Type, Weight, Sequence
FROM MyTable

SELECT ReptID, Grade, Type, Weight, Sequence
FROM #Report tR
WHERE NOT EXISTS (SELECT NULL
                  FROM #Report t2R
                  WHERE t2R.ReptID = tR.ReptID + 1
                      AND t2R.Item = tR.Item)

DROP TABLE #Report
This should return exactly what you want but without the COUNT column. If you want the count column I'll see if I can come up with something.

regards
David Cameron
 
Old July 10th, 2003, 02:36 AM
Authorized User
 
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think a rowset with the Type & Grade & Weight & Sequence is insufficient for kilika, since he (she) wants to 'group by' so some totals can be calculated.

here is my solution: (i hope this is not to late)

i assume that the data that needs grouping is in a table named T1. also, i assume that 'next to each other' is relative to sequence.

you need a supplementary column (i called it GroupNo of type int) in the T1 table.

code:

Code:
set nocount on

--change variable data types acording to your data types
declare @Item char(10), @Grade char(10), @Type char(10),
    @Weight char(10), @GroupNo int
declare @ItemLast char(10), @GradeLast char(10), @TypeLast char(10),
    @WeightLast char(10)

declare @First bit

declare crsT1 cursor local for
    select Item, Grade, Type, Weight
    from T1
    order by Sequence

open crsT1

fetch next from crsT1 into @Item, @Grade, @Type, @Weight

set @First = 1

while @@fetch_status = 0
begin
    if @First = 1
    begin
        set @GroupNo = 1
        set @First = 0
    end
    else if @Item <> @ItemLast or @Grade <> @GradeLast or @Type <> @TypeLast
        or @Weight <> @WeightLast
    begin
        set @GroupNo = @GroupNo + 1
    end

    update T1
    set GroupNo = @GroupNo
    where current of crsT1

    set @ItemLast = @Item
    set @GradeLast = @Grade
    set @TypeLast = @Type
    set @WeightLast = @Weight

    fetch next from crsT1 into @Item, @Grade, @Type, @Weight
end

select Item, Grade, Type, Weight, min(Sequence) as Sequence, count(*) as Count
from T1
group by Item, Grade, Type, Weight, GroupNo
order by min(Sequence)





Similar Threads
Thread Thread Starter Forum Replies Last Post
request forwarging & request redirection hafizmuhammadmushtaq Servlets 2 April 24th, 2008 12:42 AM
Request.Form / Request.QueryString Toran Classic ASP Databases 4 January 17th, 2007 02:23 PM
Protect cells and allow grouping/un-grouping sfreuden Excel VBA 4 December 14th, 2006 08:01 AM
request.qurystring vs. request.form Durwood Edwards Classic ASP Databases 3 June 18th, 2004 12:09 AM
request.querystring() , request.form() alyeng2000 ASP.NET 1.0 and 1.1 Basics 1 December 30th, 2003 12:07 AM





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