Transforming multiple rows into a single row

Question

In my company, I need to join table A to table B:

• Table A is the sources table
• Table B is the empty table

I want to know that how can I do it and the result like table B?

Source table A:

ID date money HOUR
-----------------------------
1 20-1 10 1
1 20-1 20 2
1 20-1 30 3
1 20-1 40 4
1 21-1 50 1
1 21-1 10 2
1 21-1 20 3
1 21-1 30 4
2 20-1 40 1
2 20-1 50 2
2 20-1 10 3
2 20-1 20 4

Empty table B:

ID date Hour1 Hour2 Hour3 Hour4 Sum
-------------------------------------------------
1 20-1 10 20 30 40 100
1 21-1 50 10 20 30 110
2 20-1 40 50 10 20 120

• Aaron Bertrand: Please see this post for some ideas.

Answer 1

You must pivot your data.

---

This can be done using the Pivot operator:

SELECT ID, [date]
 , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4
 , total as [Sum]
FROM (
 SELECT * FROM data d
 CROSS APPLY (SELECT total = SUM([money]) FROM data
 WHERE [date] = d.[date] AND ID = d.[id]) a
) t
PIVOT (
 MAX([money])
 FOR [hour] IN ([1], [2], [3], [4])
) piv

You are using SQL Server 2005 and therefore the total is calculated using CROSS APPLY. See SQL Fiddle.

---

Without PIVOT, you can also use a GROUP BY with CASEs:

SELECT ID, [date]
 , Hour1 = SUM(CASE WHEN [hour] = 1 THEN [money] END)
 , Hour2 = SUM(CASE WHEN [hour] = 2 THEN [money] END)
 , Hour3 = SUM(CASE WHEN [hour] = 3 THEN [money] END)
 , Hour4 = SUM(CASE WHEN [hour] = 4 THEN [money] END)
 , [Sum] = SUM([money])
FROM data d
GROUP BY ID, [date]

See SQL Fiddle.

---

With SQL Server 2012 you could easily calculate the total using the SUM(...) OVER(...) window function:

SELECT ID, [date], [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4, [Sum]
FROM (
 SELECT *, [Sum] = SUM([money]) OVER(PARTITION BY [date], ID) FROM data
) d
PIVOT (
 MAX([money])
 FOR [hour] IN ([1], [2], [3], [4])
) piv

See SQL Fiddle.

Note that a window function with an aggregate such as SUM(...) OVER(...) also works with SQL Server 2005 when the OVER(...) clause only contains a PARTITION BY ... and no ORDER BY .... See OVER Clause (2005) (Thanks Andriy M for the link).

---

Output:

ID date Hour1 Hour2 Hour3 Hour4 Sum
1 20-1 10 20 30 40 100
1 21-1 50 10 20 30 110
2 20-1 40 50 10 20 120

Answer 2

Try something like this:

SELECT ID
 ,[date]
 ,SUM(case when hour = 1 THEN money END) AS Hour1
 ,SUM(case when hour = 2 THEN money END) AS Hour2
 ,SUM(case when hour = 3 THEN money END) AS Hour3
 ,SUM(case when hour = 4 THEN money END) AS Hour4
 ,SUM(money) AS [Sum]
FROM data
GROUP BY ID, [date]