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

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]