1

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
4

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
1

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.