# Transforming multiple rows into a single row

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
• Please see this post for some ideas. Jan 22, 2016 at 15:28

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

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]