Alternative to multiple SQL self-joins to transform a table

Question

Currently I'm using inner joins to join a table against itself multiple times to transform a table from multiple rows per ID to one row per ID. I'm running into performance issues , as I'm joining a relatively large table against itself 6 times on a slow server.

Here's a simplified example of the query I'm currently using:

SELECT T1.id, T1.value AS 'report', T2.value AS 'area', T3.value AS 'reason', T4.value AS 'maincause' 
FROM data_table T1 
INNER JOIN data_table T2 on T1.id = T2.id AND T2.propname = 'area'
INNER JOIN data_table T3 on T1.id = T3.id AND T3.propname = 'reason'
INNER JOIN data_table T4 on T1.id = T4.id AND T4.propname = 'maincause'
WHERE T1.propname = 'report'

To transform this table:

   id    |  propname  |   value             
  ---------------------------------         
  11037  |    area    |    ABC               
  11037  |   reason   |   short               
  11037  |  maincause | electrical
  11037  |   report   |    yes
   7539  |    area    |    DEF
   7539  |   report   |    no
   7539  |  maincause | mechanical
   7539  |   reason   |   motor

To this:

   id   |  report  |  area  | reason |  maincause  
-----------------------------------------------------
 11037  |   yes    |  ABC   |  short |  electrical  
  7539  |   no     |  DEF   |  motor |  mechanical

Is there a more efficient way to do this than repeatedly joining the table against itself?

see: https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — Eray Balkanli, Dec 05 '19 at 19:56
If there is not a composite index on `(id, propname)`, I would suggest that. — Uueerdo, Dec 05 '19 at 20:23

score 2 · Answer 1 · answered Dec 05 '19 at 19:47

Try:

 select 11037 id,    'area' as propname   ,    'ABC'    as value       into #tmp    
union  select   11037  ,   'reason'   ,   'short'               
union  select   11037  ,  'maincause' , 'electrical'
  union  select 11037  ,   'report'   ,    'yes'
   union  select 7539  ,    'area'    ,    'DEF'
   union  select 7539  ,   'report'   ,    'no'
   union  select 7539  ,  'maincause' , 'mechanical'
   union  select 7539  ,   'reason'   ,   'motor'

   select * from #tmp


   -- Pivot table with one row and five columns  
SELECT id,   
[report], [area], [reason], [maincause]  
FROM  
(SELECT id, propname, [value]     
    FROM #tmp) AS SourceTable  
PIVOT  
(  
max([value])  
FOR propname       IN ([report], [area], [reason], [maincause])  
) AS PivotTable;

Reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 — Eray Balkanli, Dec 05 '19 at 19:54

score 0 · Answer 2 · answered Dec 05 '19 at 20:51

I would do something like this:

select
 t.id
 ,max(t.area) as area
 ,max(t.reason) as reason
 ,max(t.report) as report
from (
select 
t.id
,case when t.propname = 'area' then t.value else null end as area
,case when t.propname = 'reason' then t.value else null end as reason
,case when t.propname = 'report' then t.value else null end as report
from table1 as t
) as t
group by
t.id

See an example here in SQL Fiddle

Alternative to multiple SQL self-joins to transform a table

2 Answers2