I have an id in common (103307) and would like to join two rows in one. From image 1 and leave as image 2. Is it possible?
Considering that the column Untitled2
will contain the order of the line, for a same id value, this suggestion using classic pivot:
-- código #1
SELECT id,
max(case when C1 = 1 then 1 end) as nC1,
max(case when C1 = 1 then C2 end) as nC2,
max(case when C1 = 2 then 2 end) as nC3,
max(case when C1 = 2 then C2 end) as nC4
from tabela
group by id;
In the code above C1
is the column Untitled2
and C2
is the column Untitled3
, considering the first image.
As a matter of curiosity, here's another solution:
-- código #2
SELECT T1.id,
T1.C1 as nC1, T1.C2 as nC2,
T2.C1 as nC3, T2.C2 as nC4
from tabela as T1
left join tabela as T2 on T1.id = T2.id
where T1.C1 = 1
and (T2.id is null or T2.C1 = 2);
Tip 1
If the untitled2
column is a sequence for records of the same id
, the query can be as follows:
select t1.untitled1
,t1.untitled2
,t1.untitled3
,t2.untitled2 as untitled3
,t2.untitled3 as untitled4
from tabela as t1
join tabela as t2 on t1.untitled1 = t2.untitled1
where t1.untitled2 < t2.untitled2;
Solution 2
If the values contained in untitled2
and untitled3
can be repeated for records of id
, such as:
untitled1 untitled2 untitled3
103307 1 10
103307 1 10
In this case, since we may have duplicate records in the table, we need to create a idrow
to distinguish the records.
Using SqlServer CTE , we can mount a subquery to add the column idrow
before doing the join to generate the final result:
with tab_temp (idrow, untitled1, untitled2, untitled3)
as
(
select row_number() over (order by untitled1) as idrow
,untitled1
,untitled2
,untitled3
from tabela
)
select t1.untitled1
,t1.untitled2
,t1.untitled3
,t2.untitled2 as untitled3
,t2.untitled3 as untitled4
from tab_temp as t1
join tab_temp as t2 on t1.untitled1 = t2.untitled1
where t1.idrow < t2.idrow;
To Test
I created an example by running online at link