CODEWITHSUNDEEP
sqlsql-servert-sqlquery-performance
Khoa
Khoa

Reputation: 63

SQL Server : merge multiple rows into 1 row

I am using SQL Server 2012 Standard and have a performance relation to merge multiple rows into one.

Example:

enter image description here

I can use below query to get data as expected but the performance is not good. Is there any other query with better performance?

WITH Data AS
(
    SELECT 88 ID, 1 AS [OrderFW],'a' AS Code,'n1' as Name UNION
    SELECT 88 ID,2 AS [OrderFW],'a' AS Code,'n2' as Name UNION
    SELECT 88 ID,3 AS [OrderFW],'a' AS Code,'n3' as Name UNION
    SELECT 99 ID,1 AS [OrderFW],'b' AS Code,'n4' as Name UNION
    SELECT 99 ID,2 AS [OrderFW],'b' AS Code,'n5' as Name
)
SELECT 
    d1.Code code1, d1.Name name1, 
    d2.Code code2, d2.Name name2,
    d3.Code code3, d3.Name name3
FROM
    Data d1
LEFT OUTER JOIN
    Data d2 ON d1.ID = d2.ID AND d2.OrderFW = 2
LEFT OUTER JOIN
    Data d3 ON d1.ID = d3.ID AND d3.OrderFW = 3
WHERE
    d1.OrderFW = 1

Upvotes: 1

Views: 1087

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would try with aggregation: 

select Id, 
       max(case when seq = 1 then code end) as code1,
       max(case when seq = 1 then name end) as name1,
       max(case when seq = 2 then code end) as code2,
       max(case when seq = 2 then name end) as name2,
       max(case when seq = 3 then code end) as code3,
       max(case when seq = 3 then name end) as name3
from (select d.*,
             row_number() over (partition by code order by order) as seq
      from data d
     ) d
group by Id;

EDIT : If you have a already sequence (i.e. Order) then only aggregation is enough : 

select Id, 
       max(case when [order] = 1 then code end) as code1,
       max(case when [order] = 1 then name end) as name1,
       max(case when [order] = 2 then code end) as code2,
       max(case when [order] = 2 then name end) as name2,
       max(case when [order] = 3 then code end) as code3,
       max(case when [order] = 3 then name end) as name3
from data d
group by Id;

EDIT : Thanks to JohnLBevan for Demo.

Here is SQL Fiddle Example.

Upvotes: 3

Related Questions