Hi all

I have values in Table1 as

Col1

2
13
4
1
6
10

I need the result set as

Col1 Col2

2 2 -> Put the same value as Col1,Row1 i.e. 2 itself
13 15-(On Adding 2 from col2,row1 + Col1,row2)i.e.(2+13)
4 19-(On Adding 15 from col2,row2 + Col1,row3)i.e.(15+4)
1 20-(On Adding 19 from col2,row3 + Col1,row4)i.e.(19+1)
6 26-(On Adding 20 from col2,row4 + Col1,row5)i.e.(20+6)
10 36-(On Adding 26 from col2,row5 + Col1,row6)i.e.(26+10)

The Script for creating the Table


CREATE TABLE [dbo].[Table1] (
[Col1] [decimal](5, 2) NULL
) ON [PRIMARY]
GO


Table1

INSERT INTO [Table1] ([Col1])VALUES(2)
INSERT INTO [Table1] ([Col1])VALUES(13)
INSERT INTO [Table1] ([Col1])VALUES(4)
INSERT INTO [Table1] ([Col1])VALUES(1)
INSERT INTO [Table1] ([Col1])VALUES(6)
INSERT INTO [Table1] ([Col1])VALUES(10)

Please help

Thanks in advance
Anand

Report this post to a moderator

Guru,

You can use almost the same solution as I provided you with yesterday for
the post "Simple Sql Statement".

The only thing you need is a column by which you can order so that the
values appear in the order that you want them. If you don't have than it is
not possible, because SQL Server is not guaranteed to return rows in a
specific order unless you explicitly use an ORDER BY clause.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Guru S. Anand" <gurusanand@hotmail.com> wrote in message
news:010701c2a285$c1d35d80$8af
82ecf@TK2MSFTNGXA03...
> Hi all
>
> I have values in Table1 as
>
> Col1
>
> 2
> 13
> 4
> 1
> 6
> 10
>
> I need the result set as
>
> Col1 Col2
>
> 2 2 -> Put the same value as Col1,Row1 i.e. 2 itself
> 13 15-(On Adding 2 from col2,row1 + Col1,row2)i.e.(2+13)
> 4 19-(On Adding 15 from col2,row2 + Col1,row3)i.e.(15+4)
> 1 20-(On Adding 19 from col2,row3 + Col1,row4)i.e.(19+1)
> 6 26-(On Adding 20 from col2,row4 + Col1,row5)i.e.(20+6)
> 10 36-(On Adding 26 from col2,row5 + Col1,row6)i.e.(26+10)
>
> The Script for creating the Table
>
>
> CREATE TABLE [dbo].[Table1] (
> [Col1] [decimal](5, 2) NULL
> ) ON [PRIMARY]
> GO
>
>
> Table1
>
> INSERT INTO [Table1] ([Col1])VALUES(2)
> INSERT INTO [Table1] ([Col1])VALUES(13)
> INSERT INTO [Table1] ([Col1])VALUES(4)
> INSERT INTO [Table1] ([Col1])VALUES(1)
> INSERT INTO [Table1] ([Col1])VALUES(6)
> INSERT INTO [Table1] ([Col1])VALUES(10)
>
> Please help
>
> Thanks in advance
> Anand

Report this post to a moderator

Your table & expected result does not make sense. Note that
you have no way of knowing the order of rows which are returned
and hence without additional information, such cumulative values
based on a specific sequence is not possible.

However, you can add a column which defines the sort order in
your table and you can try:

CREATE TABLE [dbo].[tbl] (
[Col1] [decimal](5, 2) NULL ,
[sortcol] INT NOT NULL,
) ON [PRIMARY]
GO

INSERT INTO [tbl] ([Col1], [sortcol])VALUES(2, 1)
INSERT INTO [tbl] ([Col1], [sortcol])VALUES(13, 2)
INSERT INTO [tbl] ([Col1], [sortcol])VALUES(4, 3)
INSERT INTO [tbl] ([Col1], [sortcol])VALUES(1, 4)
INSERT INTO [tbl] ([Col1], [sortcol])VALUES(6, 5)
INSERT INTO [tbl] ([Col1], [sortcol])VALUES(10, 6)

Now the query:

SELECT col1,
(SELECT SUM(t1.Col1)
FROM tbl t1
WHERE t1.sortCol <= tbl.sortCol)
FROM tbl
ORDER BY sortCol

--
- Anith
(Please respond only to newsgroups)

Report this post to a moderator

Hi Anith

Thanks a lot..I solved as per yuor suggestion by adding a
Sequence number in the table.
Thanks,
Anand.
>-----Original Message-----
>Your table & expected result does not make sense. Note
that
>you have no way of knowing the order of rows which are
returned
>and hence without additional information, such cumulative
values
>based on a specific sequence is not possible.
>
>However, you can add a column which defines the sort
order in
>your table and you can try:
>
>CREATE TABLE [dbo].[tbl] (
> [Col1] [decimal](5, 2) NULL ,
> [sortcol] INT NOT NULL,
> ) ON [PRIMARY]
>GO
>
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(2, 1)
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(13, 2)
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(4, 3)
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(1, 4)
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(6, 5)
>INSERT INTO [tbl] ([Col1], [sortcol])VALUES(10, 6)
>
>Now the query:
>
>SELECT col1,
> (SELECT SUM(t1.Col1)
> FROM tbl t1
> WHERE t1.sortCol <= tbl.sortCol)
> FROM tbl
> ORDER BY sortCol
>
>--
>- Anith
>(Please respond only to newsgroups)
>
>
>
>
>.
>

Report this post to a moderator