CODEWITHSUNDEEP
mysql
srkn sltrk
srkn sltrk

Reputation: 418

Mysql create new rows from column content on column id

Sorry, I couldn't explain on title clearly. I have a table that has rows like

    qid     |     content
    --------------------
    1         name1
    2         surname1
    3         feature1
    1         name2
    2         surname2
    3         feature2
    1         name3
    2         surname3
    3         feature3
    4         x3
    1         name4
    2         surname4
    3         feature4

I want to create an sql query related to qids to create the table below

    column1   |  column2   |  column3   |   column4
    -----------------------------------------------
    name1        surname1     feature1      NULL
    name2        surname2     feature2      NULL
    name3        surname3     feature3      x3
    name4        surname4     feature4      NULL

NULL for qid 4 is not important.

Can anyone help me please?

Upvotes: 0

Views: 75

Answers (2)

nice_dev
nice_dev

Reputation: 17805

You can use MySQL variables and If conditions while selecting rows and go according to the qid value.

  • If qid is less than the value required for a particular column, we print an empty value, else we assign it to a variable. We carry previous column values for next rows which is necessary to bring them to a combined state.

  • We reassign variables to empty values if we find qid got reset for next set of rows.

  • In the end, we do a group by column1 and take max() of every column to bring lexicographically largest values for each value of column1. This also filters out duplicate rows which aren't fully formed till 4th column.

  • Note that this works under the assumption that rows are in order of belonging to each other consecutively, meaning, surname1 can't appear after 10 rows of row of name1.

SQL:

select max(column1),max(column2),max(column3),max(column4)
from ( select if(qid = 1,@column1 := content,@column1) as column1,
              if(qid = 2,@column2 := content,if(qid < 2,@column2 := '',@column2)) as column2,
              if(qid = 3,@column3 := content,if(qid < 3,@column3 := '',@column3)) as column3,
              if(qid = 4,@column4 := content,if(qid < 4,@column4 := '',@column4)) as column4
from test , (select @column1 := '',@column2 := '',@column3 := '',@column4 := '') init_variables) derived
group by column1;

Demo: https://www.db-fiddle.com/f/wqdRkhF5uWGDCweZX4w8rS/0

Upvotes: 1

Alex
Alex

Reputation: 17289

You should definitely normalize your data. The approach I've made is very fragile, because you have no clear GROUP BY column to group records in one line.

But so far, just to give you some direction:

https://www.db-fiddle.com/f/qPzJGtFy6yZDACtaubYJbo/0

SELECT 
     MAX(t.name),
     MAX(t.surname),
     MAX(t.feature),
     MAX(t.x)
FROM (
SELECT
   IF(t.qid = 1, @row:=@row+1, @row) `row`,
   IF(t.qid = 1, content, null) `name`,
   IF(t.qid = 2, content, null) `surname`,
   IF(t.qid = 3, content, null) `feature`,
   IF(t.qid = 4, content, null) `x`
FROM t
CROSS JOIN (SELECT @row := 0) row) t
GROUP BY t.row;

You've said in comment that you have some auto incremental column. You can use that to order in subquery:

...
CROSS JOIN (SELECT @row := 0) row
ORDER BY my_autoincremental_column) t
...

Upvotes: 0

Related Questions