locked
locate a record from a union all table RRS feed

  • Question

  • User363578564 posted

    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.

    Monday, July 9, 2018 9:23 AM

Answers

  • User753101303 posted

    Hi,

    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 )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 9, 2018 9:57 AM

All replies

  • User753101303 posted

    Hi,

    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 )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 9, 2018 9:57 AM
  • User363578564 posted

    thanks a lot for your detailed explanation

    it works now

    Monday, July 9, 2018 10:11 AM
  • User77042963 posted

    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'),
    (456,'tester2','223456789','987654322')
     
     
     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

    Monday, July 9, 2018 1:40 PM