2

Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below:

Original Table

Now, I want to 'merge' the rows according to the Year and Month value and to store them into another table, as shown below:

enter image description here

Note that, M1 means Mth = 1 (January), M2 means Mth = 2 (February), and so on. (The images are meant for description, you may ignore the accuracy of the figure.)

Question, how to do this in SQL Server?

0
4

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation:

SELECT
  UserID,
  Year,
  M1  = SUM(CASE Mth WHEN  1 THEN Value END),
  M2  = SUM(CASE Mth WHEN  2 THEN Value END),
  M3  = SUM(CASE Mth WHEN  3 THEN Value END),
  M4  = SUM(CASE Mth WHEN  4 THEN Value END),
  M5  = SUM(CASE Mth WHEN  5 THEN Value END),
  M6  = SUM(CASE Mth WHEN  6 THEN Value END),
  M7  = SUM(CASE Mth WHEN  7 THEN Value END),
  M8  = SUM(CASE Mth WHEN  8 THEN Value END),
  M9  = SUM(CASE Mth WHEN  9 THEN Value END),
  M10 = SUM(CASE Mth WHEN 10 THEN Value END),
  M11 = SUM(CASE Mth WHEN 11 THEN Value END),
  M12 = SUM(CASE Mth WHEN 12 THEN Value END)
FROM
  dbo.YourTable
GROUP BY
  UserID,
  Year
;

SQL Server also supports the PIVOT operator (since the 2005 version). I can see two distinct approaches using PIVOT here. One is probably more straightforward:

SELECT
  UserID,
  Year,
  M1  =  [1],
  M2  =  [2],
  M3  =  [3],
  M4  =  [4],
  M5  =  [5],
  M6  =  [6],
  M7  =  [7],
  M8  =  [8],
  M9  =  [9],
  M10 = [10],
  M11 = [11],
  M12 = [12]
FROM
  dbo.YourTable
PIVOT
  (
    SUM(Value)
    FOR Mth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
  ) AS p
;

Note that the above method assumes your table has no other columns apart from those shown in your example (UserID, Year, Mth, Value). If there are other columns, replace dbo.YourTable with a derived table pulling only those four columns:

  ...
FROM
  (
    SELECT
      UserID,
      Year,
      Mth,
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  ...

The other, slightly less obvious, option would be to use a derived table to construct the final Mn columns and thus avoid the renaming in the main SELECT:

SELECT
  UserID,
  Year,
  M1,
  M2,
  M3,
  M4,
  M5,
  M6,
  M7,
  M8,
  M9,
  M10,
  M11,
  M12
FROM
  (
    SELECT
      UserID,
      Year,
      Mth = 'M' + CAST(Mth AS varchar(11)),
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  (
    SUM(Value)
    FOR Mth IN (M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12)
  )
  UserID,
  Year
;

In this case you could also use just * in the main SELECT instead of the explicit column list.

0

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