SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sum Group By
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cvipin
Starting Member

USA
45 Posts

Posted - 10/26/2013 :  02:03:19  Show Profile  Reply with Quote
Let's say I have a table with 3 columns Col1, Col2 and Value .The data in these columns is as below

DECLARE @RptValues TABLE
(Col1 varchar(1), Col2 varchar(2), Amt decimal(18,2))


INSERT INTO @RptValues VALUES ('A','B',100)
INSERT INTO @RptValues VALUES ('B','C',200)
INSERT INTO @RptValues VALUES ('C','A',300)
INSERT INTO @RptValues VALUES ('C','D',400)
INSERT INTO @RptValues VALUES ('B','A',500)
INSERT INTO @RptValues VALUES ('A','C',500)


I want to group on Col1 and Col2 so that AB and BA as an example are treated as same. The exprected output is:

Col1 Col2 Value
A B 600
B C 200
C A 800
C D 400

OR

Col1 Col2 Value
B A 600
B C 200
A C 800
C D 400


Can you please help.

Thanks
Vipin

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 10/26/2013 :  02:51:55  Show Profile  Reply with Quote
select	Col1 = left(Col, 1), Col2 = right(Col, 1), Amt
from
(
	select	Col = case when Col1 < Col2 then Col1 + Col2 else Col2 + Col1 end, Amt = sum(Amt)
	from	@RptValues
	group by case when Col1 < Col2 then Col1 + Col2 else Col2 + Col1 end
) r



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/26/2013 :  04:51:21  Show Profile  Reply with Quote

SELECT COALESCE(t1.Col2,t.Col1) AS Col1,COALESCE(t1.Col1,t.Col2) AS Col2,SUM(Amt) AS Total
FROM Table t
OUTER APPLY (SELECT Col1,Col2
             FROM @RptValues
             WHERE Col1 = t.Col2
             AND Col2 = t.Col1
             AND Col1 > Col2
             )t1
GROUP BY COALESCE(t1.Col2,t.Col1),COALESCE(t1.Col1,t.Col2)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

cvipin
Starting Member

USA
45 Posts

Posted - 10/26/2013 :  10:32:56  Show Profile  Reply with Quote
Thanks khtan, your solution works great.
visakh16, your solution gives the same results as source table.

Thanks
Vipin
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/26/2013 :  15:18:14  Show Profile  Reply with Quote
Sorry tht was a typo

see this illustration

DECLARE @RptValues TABLE
(Col1 varchar(1), Col2 varchar(2), Amt decimal(18,2))


INSERT INTO @RptValues VALUES ('A','B',100)
INSERT INTO @RptValues VALUES ('B','C',200)
INSERT INTO @RptValues VALUES ('C','A',300)
INSERT INTO @RptValues VALUES ('C','D',400)
INSERT INTO @RptValues VALUES ('B','A',500)
INSERT INTO @RptValues VALUES ('A','C',500)

SELECT COALESCE(t1.Col1,t.Col1) AS Col1,COALESCE(t1.Col2,t.Col2) AS Col2,SUM(Amt) AS Total
FROM @RptValues t
OUTER APPLY (SELECT Col1,Col2
             FROM @RptValues
             WHERE Col1 = t.Col2
             AND Col2 = t.Col1
             AND Col1 > Col2
             )t1
GROUP BY COALESCE(t1.Col1,t.Col1),COALESCE(t1.Col2,t.Col2)
ORDER BY COALESCE(t1.Col1,t.Col1),COALESCE(t1.Col2,t.Col2)


output
---------------------------
Col1	Col2	Total
---------------------------
B	A	600.00
B	C	200.00
C	A	800.00
C	D	400.00



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000