T SQL Merge Two Lines in One

1

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?

    
asked by anonymous 22.12.2017 / 21:38

2 answers

1

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);
    
23.12.2017 / 20:49
0

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

    
23.12.2017 / 23:10