How to move database table rows into columns based on a common entry-CodePudding

I have the following database:

This is just a subset of a larger data set that would have been too big to type.

ID	CODE	IDENTIFIERS	COLORS	SHAPES	NUMB
101	24	N	RED	CIRCLE	174
102	32	N	BLUE	SQUARE	155
102	32	N	PURPLE	STAR	223
103	13	Y	RED	SQUARE	143
103	13	Y	GREEN	CIRCLE	123
103	13	Y	BLUE	SQUARE	142

I need the following result:

ID	CODE	IDENTIFIERS	COLORS1	COLORS2	COLORS3	SHAPES1	SHAPES2	SHAPES3	NUMB1	NUMB2	NUMB3
101	24	N	RED			CIRCLE			174
102	32	N	BLUE	PURPLE		SQUARE	STAR		155	223
103	13	Y	RED	GREEN	BLUE	SQUARE	CIRCLE	SQUARE	143	123	142

I tried:

    SELECT ID, 
     CODE, 
     IDENTIFIERS, 
     group_concat(COLORS) AS COLORSS, 
     group_concat(SHAPES) AS SHAPESS, 
     group_concat(NUMB) AS NUMBS 
     FROM ( 
     SELECT a.ID, 
     a.CODE, 
     a.IDENTIFIERS, 
     a.COLORS, 
     a.SHAPES, 
     a.NUMB 
     FROM database AS a 
    ) AS sub 
    GROUP BY ID, CODE, IDENTIFIERS

I got:

ID	CODE	IDENTIFIERS	COLORS	SHAPES	NUMB
101	24	N	RED	CIRCLE	174
102	32	N	BLUE,PURPLE	SQUARE,STAR	155,223
103	13	Y	RED,GREEN,BLUE	SQUARE,CIRCLE,SQUARE	143,123,142

Which is close, but not exactly what I was going for. Any help would be appreciated.

CodePudding user response：

First you need a way to identify which columns you want to put a row into. For that you can use ROW_NUMBER()

Then you can use a combination of MAX(CASE) to pivot the data.

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id, code, identifiers)   AS row_id
  FROM
    yourTable
)
SELECT
  id,
  code,
  identifiers,

  MAX(CASE WHEN row_id = 1 THEN colors END)   AS colors_1,
  MAX(CASE WHEN row_id = 1 THEN shapes END)   AS shapes_1,
  MAX(CASE WHEN row_id = 1 THEN numb   END)   AS numb_1,

  MAX(CASE WHEN row_id = 2 THEN colors END)   AS colors_2,
  MAX(CASE WHEN row_id = 2 THEN shapes END)   AS shapes_2,
  MAX(CASE WHEN row_id = 2 THEN numb   END)   AS numb_2,

  MAX(CASE WHEN row_id = 3 THEN colors END)   AS colors_3,
  MAX(CASE WHEN row_id = 3 THEN shapes END)   AS shapes_3,
  MAX(CASE WHEN row_id = 3 THEN numb   END)   AS numb_3
FROM
  sorted
GROUP BY
  id,
  code,
  identifiers