这是通过 数据透视表完成的 。按分组id
,您CASE
为要在列中捕获的每个值发出语句,并使用诸如MAX()
聚合之类的方法消除空值并折叠成一行。
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