SQL Server - Group by Values from Different Columns

Question

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 1

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

Answer 2

I like to use apply for this purpose:

select v.person, v.period, sum(v.value)
from t apply
     (values (Person1, Value1, Period),
             (Person2, Value2, Period),
             (Person3, Value3, Period)
     ) v(Person, Value, Period)
group by v.person, v.period;

Answer 3

;WITH CTE (Person , Val) AS (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 ) SELECT Person, SUM(Val) FROM CTE GROUP BY Person