SQL query which can convert Rows in to Column

Question

RuModules_Id	Info
9	1,1,49
16	1,0,50
16	2,0,51
16	3,0,52
16	4,0,53
17	1,0,54
17	2,0,55
17	3,0,56
17	4,0,57
18	1,0,58

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

Please, post your sample data and expected results as text. If you have fixed number of `M` columns, conditional aggregation or pivoting the table are possible solutions. — Zhorov, Mar 02 '22 at 07:35
You can't have arbitrary columns in a relational database, either in a table or a query. You'll have to do this on the client. A table is the actual R(elation) in RDBMS, not the foreign key *constraint*. A table specifies the relation between values. You can't have arbitrary relations. The PIVOT and UNPIVOT commands actually need to know the "column" values in advance — Panagiotis Kanavos, Mar 02 '22 at 07:42
What are you trying to do in the first place? Why do you want this? Transposing data is best done on the client. Displaying horizontal instead of vertical grids is a UI concern. It's far easier to create a "horizontal" table in HTML than try to come up with a SQL query — Panagiotis Kanavos, Mar 02 '22 at 07:45
Ok please post your data a text. A DBfiddle would be perfect. — , Mar 02 '22 at 08:03
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Stu, Mar 02 '22 at 08:52

Zhorov · Answer 1 · 2022-03-08T06:54:16.410

Possible approaches are conditional aggregtaion and PIVOT relational operator. 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

score 0 · Answer 2 · answered Mar 02 '22 at 08:15

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.

SQL query which can convert Rows in to Column

2 Answers2