How to insert a row in Sql Server

Posted by T.saravanan under Sql Server on 8/6/2010 | Views : 2579 | Status : [Member] [MVP] | Replies : 5
Hi,
I have a different group of company price value in my table in sql server database.Now i have the total price for that group of companies price value.
I want a output for the following format:

Table Details:
Group Price
A 15
A 20
A 10
B 28
B 12
C 41
C 16
C 24

Output :
Group Price
A 15
A 20
A 10
Total 45
B 28
B 12
Total 40
C 41
C 16
C 24
Total 81

How to insert the total row in between the rows of A & B.If is it possible to achieve this output to using procedure. Please any one help to me.

Advance Thanks,
T.Saravanan.

Thanks,
T.Saravanan



Responses

Posted by: Neeks on: 8/6/2010 [Member] Bronze

Up
0
Down
T.saravanan,

we cannot insert the record at the indexed position, i.e. you cannot add the row at the specified position, but you can do by inserting the data in another table. Fetch the data from the table1 and insert the necessary row in table2

T.saravanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shankul2784 on: 8/7/2010 [Member] Bronze

Up
0
Down

Hi,

This is possible using the concept of temporary table.

Guideline :
In stored procedure you can use the while loop or cursor & using that you can insert the records in the temporary table. For this your data should be sorted using the group name.

i hope, it will help you.

Thanks & Regards,

Shailesh

T.saravanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 8/7/2010 [Member] [MVP] Silver

Up
0
Down
Thanks Shankul.
If any possibilities to avoid using temp table concept.Because if no of groups are high means procedure execution takes time then only i ask any another method to achieve that output.

Thanks,
T.Saravanan

Thanks,
T.Saravanan

T.saravanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/8/2010 [Member] [MVP] Silver

Up
0
Down
Hi

Try the following Pocedure... It should work.

CREATE PROC PROC_Result

AS
BEGIN
SET NOCOUNT ON

DECLARE @Groups TABLE
(
Seq INT,
[Group] VARCHAR(10),
Price INT
);

WITH CTEs
AS(
SELECT ROW_NUMBER() OVER(ORDER BY [GROUP]) Seq,[GROUP],PRICE FROM TableName
UNION ALL
SELECT 999999,[GROUP],SUM(PRICE) PRICE FROM TableName GROUP BY [GROUP] WITH ROLLUP
)
INSERT @Groups
SELECT SEQ,[Group],Price FROM CTES WHERE [GROUP] IS NOT NULL ORDER BY [GROUP],Seq

SELECT CASE SEQ WHEN 999999 THEN 'TOTAL' ELSE [Group] END [Group],Price FROM @Groups
END
GO
EXEC PROC_Result

Group Price
A 15
A 20
A 10
TOTAL 45
B 28
B 12
TOTAL 40
C 41
C 16
C 24
TOTAL 81

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

T.saravanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 8/8/2010 [Member] [MVP] Silver

Up
0
Down
Thanks Pandian S....

Thanks,
T.Saravanan

T.saravanan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response