saf21 saf21 - 4 months ago 12
SQL Question

SQL: select column from same table

how to select from database to show this new table into page. I'm just select from the same table. Can anyone help this?

Original Table from db



Id | Username | UserType | SpecialId
-------------------------------------
1 | jdoe | Type_A | SP_1
2 | dmatt | Type_A | SP_2
3 | kwill | Type_A | SP_3
4 | kwill_a | Type_B | SP_3
5 | dmatt_a | Type_B | SP_2
6 | dmatt_b | Type_B | SP_2


New table



No | Type_A Username | Type_B Username
-------------------------------------
1 | jdoe | -
2 | dmatt | dmatt_a, dmatt_b
3 | kwill | kwill_a

JPG JPG
Answer

I think you may want to do this(Looks like a table pivot issue):

select
    replace(SpecialId, 'SP_', '') as No,
    max(case when UserType = 'Type_A' then UserName else '-' end) as Type_A_UserName,
    max(case when UserType = 'Type_B' then UserName else '-' end) as Type_B_UserName
from (
  select `UserType`, `SpecialId`, group_concat(UserName) as UserName
  from yourtable
  group by `UserType`, `SpecialId`) t
group by SpecialId

SqlFiddle Demo