Last post Jul 09, 2018 01:40 PM by limno
Jul 09, 2018 09:23 AM|TW88|LINK
the fields in source table is ID, Name1,Mobile1, Name2, Mobile2
it records 2 persons in one row.
How i have to convert to 1 person per row by union all
select ID,Name1,Mobile1 union all select ID,Name2,Mobile2
but 2 rows have same ID, i have to edit/delete one of them, so i add virtual last column to identfiy 1 or 2
select ID,Name1,Mobile1,'1' as mark union all select ID,Name2,Mobile2,'2' as mark
select the table is fine but i add further "where id =123 and mark=2"
it does not work, the virtual column cannot be used. any way out? thanks.
Jul 09, 2018 09:57 AM|PatriceSc|LINK
You created a view or trying to add directly the where criteria to the select statement? Rather than "cannot be used" or "doesn't work" it's best to always tell what happens. My guess is that you have a SQL error because the syntax is wrong.
If SQL Server you could try :
with cte as (
select ID,Name1,Mobile1,'1' as mark from data union all select ID,Name2,Mobile2,'2' from data
SELECT * fROM cte
where id=1 and mark=2
so that the mark column is defined first and then used (I assume you have an error telling the "mark" column doesn't exists )
Jul 09, 2018 10:11 AM|TW88|LINK
thanks a lot for your detailed explanation
it works now
Jul 09, 2018 01:40 PM|limno|LINK
Besides the union all solution, you can use cross apply to normalize your table. Here is an example:
create table test (id int,name1 varchar(20), mobile1 varchar(10), mobile2 varchar(10))
insert into test values(123,'tester2','123456789','987654321'),
Select id,name1,mobile from test
cross apply (values(1,mobile1),(2,mobile1) ) d(mark,mobile)
WHERE id=123 and mark=2
drop table test