# 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]
,  as Hour1,  as Hour2,  as Hour3,  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 (, , , )
) 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 `CASE`s:

``````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],  as Hour1,  as Hour2,  as Hour3,  as Hour4, [Sum]
FROM (
SELECT *, [Sum] = SUM([money]) OVER(PARTITION BY [date], ID) FROM data
) d
PIVOT (
MAX([money])
FOR [hour] IN (, , , )
) 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]
``````