Change row values from single column into different column names

I have a table, EmpDetail, with three columns:

EMP_NAME    DETAIL_ID    DETAIL
----------------------------------
ABC         ADDRESS      abc123
ABC         PHONE        12345
ABC         EMAIL        abc@xyz
BCD         ADDRESS      bcd234
BCD         PHONE        23456
BCD         EMAIL        bcd@345

I w开发者_运维百科ant to create a new table such that the values from the DETAIL_ID become the new column names:

EMP_NAME    ADDRESS    PHONE    EMAIL
----------------------------------------
ABC         abc123     12345    abc@xyz
BCD         bcd234     23456    bcd@345

I have already tried Join but i am unable to get the desired result.

Please suggest a way to do this.

Here's an example of how to join for just two details, address and phone:

insert  into NewTable
        (EMP_NAME, ADDRESS, PHONE)
select  name.EMP_NAME
,       addr.DETAIL
,       phone.DETAIL
from    (
        select  distinct EMP_NAME
        from    OldTable
        ) name
left join
        OldTable addr
on      addr.EMP_NAME = name.EMP_NAME
        and addr.DETAIL_ID = 'ADDRESS'
left join
        OldTable phone
on      phone.EMP_NAME = name.EMP_NAME
        and phone.DETAIL_ID = 'PHONE'

Grouping also works, probably a little simpler:

insert  into NewTable
        (EMP_NAME, ADDRESS, PHONE)
select  EMP_NAME
,       max(case when DETAIL_ID = 'ADDRESS' then DETAIL end)
,       max(case when DETAIL_ID = 'PHONE' then DETAIL end)
from    OldTable
group by
        EMP_NAME

CREATE TABLE NewTable AS
SELECT EMP_NAME ,
  wm_concat(ADDRESS) ADDRESS,
  wm_concat(PHONE) PHONE,
  wm_concat(EMAIL) EMAIL
FROM
  (SELECT EMP_NAME ,
    CASE
      WHEN DETAIL_ID='ADDRESS'
      THEN DETAIL
      ELSE NULL
    END ADDRESS ,
    CASE
      WHEN DETAIL_ID='PHONE'
      THEN DETAIL
      ELSE NULL
    END PHONE ,
    CASE
      WHEN DETAIL_ID='EMAIL'
      THEN DETAIL
      ELSE NULL
    END EMAIL
  FROM EmpDetail
  )
GROUP BY EMP_NAME;