Combine multiple rows based on some value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine multiple rows based on some value

Author

Topic

snagar
Starting Member

14 Posts

Posted - 2012-03-05 : 08:47:09

Hi,
I have table like below-

Grade---Value

A-------10
B-------5
A-------7
C-------4
D-------1
C-------1

And i want data like this-

Grade----Value

A/B------22
C/D------6

where value now becomes sum of all A and B value and renamed as A/B
and C/D is sum of all C and D renamed as C/D.

Please help me with T-sql query for this requirement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-05 : 08:50:49

Based on what that you combined A and B or C and D together ?

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-05 : 08:54:57

you could do this:


DECLARE @sample TABLE (
	[Grade] CHAR(1)
	, [Score] INT
	)
INSERT @sample ([Grade], [Score]) VALUES 
 (('A'), (10))
,(('B'), (5))
,(('A'), (7))
,(('C'), (4))
,(('D'), (1))
,(('C'), (1))

/*
And i want data like this-

Grade----Value

A/B------22
C/D------6
*/
; WITH combiGrades AS (
	SELECT
		CASE
			WHEN [Grade] IN ('A', 'B') THEN 'A/B'
			WHEN [Grade] IN ('C', 'D') THEN 'C/D'
		END AS [Grade]
		, [Score]
	FROM
		@sample
	)
SELECT
	[Grade] AS [Grade]
	, SUM([Score]) AS [Value]
FROM
	combiGrades
GROUP BY
	[Grade]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

snagar
Starting Member

14 Posts

Posted - 2012-03-06 : 08:23:13

quote:
Originally posted by Transact Charlie

you could do this:

DECLARE @sample TABLE (
[Grade] CHAR(1)
, [Score] INT
)
INSERT @sample ([Grade], [Score]) VALUES
(('A'), (10))
,(('B'), (5))
,(('A'), (7))
,(('C'), (4))
,(('D'), (1))
,(('C'), (1))

/*
And i want data like this-

Grade----Value

A/B------22
C/D------6
*/
; WITH combiGrades AS (
SELECT
CASE
WHEN [Grade] IN ('A', 'B') THEN 'A/B'
WHEN [Grade] IN ('C', 'D') THEN 'C/D'
END AS [Grade]
, [Score]
FROM
@sample
)
SELECT
[Grade] AS [Grade]
, SUM([Score]) AS [Value]
FROM
combiGrades
GROUP BY
[Grade]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Thanks a lot for your help!
The query gives me the required result.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 09:51:59

And for cross platform code



  SELECT CASE 
			WHEN [Grade] IN ('A','B') THEN 'A/B'
			WHEN [Grade] IN ('C','D') THEN 'C/D'
									  ELSE 'Other'
			END AS [Grade]
		  , SUM([Score])
    FROM @sample
GROUP BY 
		CASE 
				WHEN [Grade] IN ('A','B') THEN 'A/B'
				WHEN [Grade] IN ('C','D') THEN 'C/D'
										  ELSE 'Other'
		END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources