Below is a piece of SQL code I wrote. I want to merge two records in to one record where the first record domain is shown as "From source" and the second record's domain become "To Domain". There will be more filtering I need to do but why is this simple statement not working?
I get an error "Syntax error at end of input"
*SELECT
"ID" , "Time", "Source Domain", "To Domain" From
(SELECT "RecordID" As "ID","UTCTimestamp" As "Time","Domain" As "Source Domain" FROM public."Traffic - Mobile")T1
Inner Join Lateral
(SELECT "Domain" As "To Domain" FROM public."Traffic - Mobile" where "RespondentID"="T1"."RespondentID" )T2*
Thanks in advance
PostgreSQL version 9.3
I think you are overcomplicating this. A simple self join of the table is enough. You don't need a derived table to rename a column
SELECT "ID", "Time", t1."Domain" as "Source Domain", t2."Domain" as "To Domain"
from public."Traffic - Mobile" as t1
join public."Traffic - Mobile" as t2 on t2."RespondentID" = t1."RespondentID";