Reb.Z Reb.Z - 29 days ago 6
SQL Question

SQL Server - Group by Values from Different Columns

I have a set of data like this:

ID Person1 Value1 Person2 Value2 Person3 Value3 Period
---------------------------------------------------------------------
1 A 1 D 1 June
2 B 2 July
3 C 3 June
4 B 1 C 3 A 2 August
5 C 2 A 2 June


Column ID is just a row identifier and doesn't mean anything. I want to group them by Person and Period and followed by the sum of each person's value. The expected result would look like:

Person Period Value
-----------------------
A June 3
A August 2
B July 2
B August 1
C June 5
C August 3
D June 1


Is it doable? Any help would be appreciated!

Answer

Based on question edit and comment:

SELECT Person, Period, SUM(Val) as total
FROM (
  SELECT Person1 as Person, Period, Value1 as Val
  FROM tablename
  UNION ALL
  SELECT Person2 as Person, Period, Value2 as Val
  FROM tablename
  UNION ALL
  SELECT Person3 as Person, Period, Value3 as Val
  FROM tablename
) sub
GROUP BY Person, Period

Original answer

Reduce the problem to steps to solve.

First Normalize

SELECT Person1 as Person, Value1 as Val
FROM tablename
UNION ALL
SELECT Person2 as Person, Value2 as Val
FROM tablename
UNION ALL
SELECT Person3 as Person, Value3 as Val
FROM tablename

Then add em up with group by like normal

SELECT Person, SUM(Val) as total
FROM (
  SELECT Person1 as Person, Value1 as Val
  FROM tablename
  UNION ALL
  SELECT Person2 as Person, Value2 as Val
  FROM tablename
  UNION ALL
  SELECT Person3 as Person, Value3 as Val
  FROM tablename
) sub
GROUP BY Person