MySql query to dynamically convert rows to columns
In some cases pivoting a table is not desired, instead we need to convert rows to columns and append them to the result set without “grouping” them against a specific value. In some cases we simply need to append a column, or columns, for each row and name the new columns something arbitrary like data1, data2, data3, etc.
For example lets consider the following dataset:
The final result should be something like this:
Convert rows to columns using LEFT JOIN
The main idea is to use multiple LEFT JOINs on the same dataset, for each row we need to convert to column. We are using LEFT JOIN instead of INNER because we want the query to return NULL in some cases.
Of course since we don’t know the number of rows to be converted beforehand we must use prepared statements and some string functions like CONCAT and GROUP_CONCAT in order to create the SQL statement and then execute it.
The query
SET group_concat_max_len=20000; SET @a = 0; SET @b = 0; set @num := 0; SET @regno := ''; SET @line1 = CONCAT ( 'SELECT ', ( SELECT CONCAT('regno,', GROUP_CONCAT(' s',@a:=@a+1,', m',@a,', rk',@a)) FROM test1 as ts1 WHERE regno = ( SELECT regno FROM test1 tm GROUP BY regno ORDER BY count(tm.subject) DESC LIMIT 1 ) ), ' FROM test1' , ( SELECT CONCAT(' ', REPLACE(REPLACE(GROUP_CONCAT(' LEFT JOIN ( SELECT regno as reg',@b:=@b+1,'| subject as s',@b,'| medium as m',@b,'| remark as rk',@b,' FROM ( SELECT *| @num := IF(@regno = regno| @num + 1| 1) AS row_number| @regno := regno AS dummy FROM test1 ORDER BY regno) AS x',@b,' WHERE x',@b,'.row_number = ',@b,') as t',@b,' ON reg',@b,' = regno'),',',' '),'|',',')) FROM test1 as ts2 WHERE regno = ( SELECT regno FROM test1 tm GROUP BY regno ORDER BY count(tm.subject) DESC LIMIT 1 ) ), ' GROUP BY regno' ); PREPARE my_query FROM @line1; EXECUTE my_query;
I want to know if transposing with dynamic columns can be done using view? If yes, can you please help on this.
Can you help me in converting the data of gridview that comes from database from row to column and column to row using asp.net c#
thank’s for share