Grouping multiple rows into 1 row during a select into 
Author Message
 Grouping multiple rows into 1 row during a select into

I have a table which looks something like this:

interval    value 1    value2
=====    ======    =====
1                25            25
2                15            15

I need to able to select from this table into another temp table and at the
same time group the rows together, by any multiple, to look something like
this:

interval    value 1    value2
=====    ======    =====
2                40            40

You can see that the above row has retained the last interval value and then
did a sum on the other two columns.

Is there any SQL statement or attribute that I can use to coerce the data
into this shape?

Also, I need to be able to do on tables with millions of rows.

Any help is very much appreciated.

Cheers
Rob



Fri, 03 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into

Hi Robert,

SELECT
    MAX(interval) AS interval,
    SUM(value1) AS value1,
    SUM(value2) AS value2
INTO
    #temp_summary
FROM
    interval_log
GO

Best Regards

Trevor Dwyer - SQL Server MVP

Quote:

>I have a table which looks something like this:

>interval    value 1    value2
>=====    ======    =====
>1                25            25
>2                15            15

>I need to able to select from this table into another temp table and at the
>same time group the rows together, by any multiple, to look something like
>this:

>interval    value 1    value2
>=====    ======    =====
>2                40            40

>You can see that the above row has retained the last interval value and
then
>did a sum on the other two columns.

>Is there any SQL statement or attribute that I can use to coerce the data
>into this shape?

>Also, I need to be able to do on tables with millions of rows.

>Any help is very much appreciated.

>Cheers
>Rob



Fri, 03 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into
Thanks for the reply, perhaps I should be clearer in what I want to do.  I
need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc.  The regular
aggregate functions don't seem to give me the ability to do this.

Thanks again.
Rob



Fri, 03 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into
Well, that was a fun problem.  Its not an elegant solution, but I think this
does what you are trying to do:





begin

    from tbl

  insert into #tbl2 (interval, v1, v2)

end

-- get the last row if needed

 insert into #tbl2 (interval, v1, v2)

   from tbl

select * from #tbl2

Good Luck

Janice

Quote:

>Thanks for the reply, perhaps I should be clearer in what I want to do.  I
>need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc.  The regular
>aggregate functions don't seem to give me the ability to do this.

>Thanks again.
>Rob



Sun, 05 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into
Hi Robert Chapman,

Well if ur first column has the value in sequence, this will work

 Select Case When Round(<Col1>/2,0)*2=<Col1> then <Col1>-1
             Else Col
        End, sum(<col2>)
 From <Table>
 Group by (Case When Round(<Col1>/2,0)*2=<Col1> then <Col1>-1
             Else Col
        End)
hope this meets ur requirement

Regards
Dev

Quote:

> Well, that was a fun problem.  Its not an elegant solution, but I think this
> does what you are trying to do:





> begin

>     from tbl

>   insert into #tbl2 (interval, v1, v2)

> end

> -- get the last row if needed

>  insert into #tbl2 (interval, v1, v2)

>    from tbl

> select * from #tbl2

> Good Luck

> Janice


> >Thanks for the reply, perhaps I should be clearer in what I want to do.  I
> >need to sum rows 1 and 2, then 3 and 4, then 5 and 6 etc.  The regular
> >aggregate functions don't seem to give me the ability to do this.

> >Thanks again.
> >Rob



Sun, 05 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into
On Mon, 15 Feb 1999 11:28:47 -0500, "Robert Chapman"

Quote:

>Also, I need to be able to do on tables with millions of rows.

I scan 5m records, do a group by producing 800k records, then walk the
resultset with a cursor inserting into another table, all in about
forty minutes.

I was working on the same thing done without the cursor, but ran
across a bug and, although I had a workaround, dropped it.  Doubt it
would be much faster.

On a dual processor Compaq with 2gigabytes of RAM.

Joshua Stern



Mon, 06 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into

Quote:
>On a dual processor Compaq with 2gigabytes of RAM.
>Joshua Stern

Makes my proposed configuration of 512meg RAM look very cheapskate indeed!


Mon, 06 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into


Quote:
>>On a dual processor Compaq with 2gigabytes of RAM.
>>Joshua Stern

>Makes my proposed configuration of 512meg RAM look very cheapskate indeed!

Some slow afternoon, I'm going to {*filter*}SQLServer down to 128mb and
rerun some of my code, see what different it makes!

Joshua Stern



Tue, 07 Aug 2001 03:00:00 GMT
 Grouping multiple rows into 1 row during a select into


Quote:
>>On a dual processor Compaq with 2gigabytes of RAM.
>>Joshua Stern

>Makes my proposed configuration of 512meg RAM look very cheapskate indeed!

Some slow afternoon, I'm going to {*filter*}SQLServer down to 128mb and
rerun some of my code, see what different it makes!

Joshua Stern



Tue, 07 Aug 2001 03:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. question regarding select (multiple rows select into one result row)

2. concatenate varchar field from multiple rows into one row as a group

3. select case return rows in one row (how to group by three column)

4. SELECT multiple rows back as one row with many columns

5. column in multiple rows to multiple columns in one row

6. column in multiple rows to multiple columns in one row

7. How to get only the first row by GROUP in a SQL SELECT with GROUP BY

8. More SQL, Row count during select

9. More SQL, Row count during select

10. HELP! UPDATE single-row subquery returns multiple rows

11. DTS- multiple destination rows for each source row

12. Pivoting one source row into multiple destination rows


 
Powered by phpBB® Forum Software