How to insert a row in Sql Server

Posted by T.saravanan under Sql Server on 8/6/2010 | Views : 3846 | Status : [Member] [MVP] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

0	Thanks Pandian S.... Thanks, T.Saravanan T.saravanan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts