Setting column values as column names in the SQL q

2019-01-06 18:39发布

I wanted to read a table which has values which will be the column names of the sql query result. For example, I have table1 as ..

id    col1     col2
----------------------
0      name    ax
0      name2   bx
0      name3   cx
1      name    dx
1      name2   ex
1      name2   fx

If u see for id = 0, name has value of ax and name 2 - bx and name3 = cx instead of this being rows it would be easier to show columns as id, name, name2, name3 Now I want the result of the query to look like this

id   name    name2     name3
0    ax      bx         cx
1    dx      ex         fx

Can someone help me in achieving this ?

标签: mysql sql pivot
3条回答
做个烂人
2楼-- · 2019-01-06 19:00

This is done with a pivot table. Grouping by id, you issue CASE statements for each value you want to capture in a column and use something like a MAX() aggregate to eliminate the nulls and collapse down to one row.

SELECT
  id,
  /* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
  /* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
  MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
  MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM
  yourtable
GROUP BY id
ORDER BY id

Here's a working sample

Note: This only works as is for a finite and known number of possible values for col1. If the number of possible values is unknown, you need to build the SQL statement dynamically in a loop.

查看更多
Melony?
3楼-- · 2019-01-06 19:06
select id,
    max(if(tablename.columnname = 'name',tablename.columnname,null)) as namealise,
    max(if(tablename.columnname = 'name1',tablename.columnname,null)) as namealise1 
from table1, table2 
where table1.id = table2.id 
group by table1.id 
order by table1.id
查看更多
欢心
4楼-- · 2019-01-06 19:17

What you are attempting to do is a PIVOT MySQL does not have a PIVOT function so you can replicate this using a CASE and an aggregate function.

If you have a known number of columns, then you can use a static version and hard-code the values. Similar to this (See SQL Fiddle with demo):

select id,
  max(case when col1='name' then col2 end) name,
  max(case when col1='name2' then col2 end) name2,
  max(case when col1='name3' then col2 end) name3
from yourtable
group by id

But if you have an unknown number of columns, then you can use a prepared statement and create this dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col1 = ''',
      col1,
      ''' then col2 end) AS ',
      col1
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM yourtable 
                  GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

查看更多
登录 后发表回答