I need to convert the Table A to Table B. Can someone help me how to do it in SQL query?

CodePudding user response：

Possible approaches are conditional aggregtaion and PIVOT. You need to number the rows and group them appropriately (even if you have more than 4 rows for each RuModules_Id):

Statement with conditional aggregation:

SELECT 
   RuModules_Id,
   M1 = MAX(CASE WHEN RN % 4 = 0 THEN Info END),
   M2 = MAX(CASE WHEN RN % 4 = 1 THEN Info END),
   M3 = MAX(CASE WHEN RN % 4 = 1 THEN Info END),
   M4 = MAX(CASE WHEN RN % 4 = 2 THEN Info END)
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1 AS Rn
   FROM Data
) t 
GROUP BY RuModules_Id, Rn / 4

Statement with PIVOT:

SELECT RuModules_Id, M1, M2, M3, M4
FROM (
   SELECT 
      (ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1) / 4 AS Id,
      *, 
      CASE (ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1) % 4
         WHEN 0 THEN 'M1'
         WHEN 1 THEN 'M2'
         WHEN 2 THEN 'M3'
         WHEN 3 THEN 'M4'
      END AS M
   FROM Data
) t
PIVOT (  
   MAX(Info)  
   FOR M IN ([M1], [M2], [M3], [M4])  
) p

CodePudding user response：

Depends on which SQL database you are using.

Also, your table A is missing a column containing "M1", "M2" etc. In my solution below, I expect the data structure to be:

ModuleId | RuModules_Id | Info
M1 | 9 | 1,1,49

If you are using SQL Server, you can do a pivot.

The syntax would be something like:

SELECT * FROM (
  SELECT
    [ModuleId]
    [RuModules_Id],
    [Info]
  FROM A
) A_sub
PIVOT (
  MAX([Info])
  FOR [ModuleId]
  IN (
    [M1],
    [M2],
    [M3],
    [M4]
  )
) AS PivotTable

In the example above (not tested), it would build the columns M1,M2,M3,M4 add a row for each unique value found in RuModules_Id and in each sell the max value found in [Info] for the specific combo of ModuleId and RuModules_Id would be listed.

It is not possible to do a dynamic solution with raw SQL. You need to know which exact columns you create (in our example we created M1, M2, M3, M4. M5 and above will not be included in the result.

If you want it to be dynamic, you can write a stored procedure (if using MS SQL server for example), that generates a query based on some input. The string "[M1],[M2],[M3],[M4]" would then be the input. The stored procedure could then be used in another stored procedure, that builds the list of colums.