Hemin Hemin - 3 months ago 8
MySQL Question

MySQL create view: duplicate rows which has different value in 4 columns and not empty

I have a table like :

CPID CID U1 U2 U3 U4
1 44 Day Hour Null Year
2 45 Day Year Null Null


i want to make a view to duplicate row if has more than one kind of unit (Ux)

the above will be like this:

CPID CID Ux
1 44 Day
1 44 Hour
1 44 Year
2 45 Day
2 45 Year


what i did so far is only :

SELECT CPID AS CPID,
CID AS CID,
Concat_Ws(',', T1, T2, T3, T4) AS Ux


above will only show :

CPID CID Ux
1 44 Day,Hour,Year
2 45 Day,Year


i am still new in MySQL and i think making duplicate/add/edit is not possible in view i would be thankful for your suggestions.

Answer

Do a UNION ALL, one SELECT for each Un column:

select CPID, CID, UX
from
(
select CPID, CID, U1 as UX from tablename
union all
select CPID, CID, U2 as UX from tablename
union all
select CPID, CID, U3 as UX from tablename
union all
select CPID, CID, U4 as UX from tablename
) dt
where ux is not null

And, if the result-set order is of importance, add the following row at the end:

order by CPID, CID, Ux

Seems like MySQL didn't like the above query's derived table when CREATE VIEW. Here comes another try:

create view viewname (CPID, CID, UX) AS
select CPID, CID, U1 from tablename
where U1 is not null
union all
select CPID, CID, U2 from tablename
where U2 is not null
union all
select CPID, CID, U3 from tablename
where U3 is not null
union all
select CPID, CID, U4 from tablename
where U4 is not null
Comments