getting rid of NULL rows when transforming a row into a column

Question

I've got the following SQL code:

SELECT TOP (1000) 
  a.[JourneyNumber]
  ,a.[JourneyDate]
  ,a.[tReg_ID]
  ,a.[Reg]
  ,a.[ID]
  ,b.tLocationPosition_ID
  ,c.LifeCode
  ,c.LifeTotal

  ,CASE WHEN c.LifeCode LIKE 'LCF1' AND b.tLocationPosition_ID = 1 THEN c.LifeTotal END as ALCF1
  ,CASE WHEN c.LifeCode LIKE 'LCF1' AND b.tLocationPosition_ID = 2 THEN c.LifeTotal END as BLCF1
FROM [RALNHVTST].[dbo].[tRegJourney] as a

LEFT JOIN [RALNHVTST].[dbo].[tRegJourneyLogBook] as b
ON a.ID = b.tRegJourney_ID

LEFT JOIN [RALNHVTST].[dbo].[tRegJourneyLogBookLifeCodeEvents] AS c
ON b.ID = c.tRegJourneyLogBook_ID

WHERE b.tLocation_ID = 720
AND a.tReg_ID = 73 OR a.tReg_ID = 38
AND (b.tLocationPosition_ID = 1 OR b.tLocationPosition_ID = 2)
AND (c.LifeCode LIKE 'LCF1' )
ORDER BY JourneyDate

In the SELECt statement I'm trying to transform multiple rows that mainly contain the same information into columns.

So that this:

JourneyNo JourneyDate       Reg    ID   tLocationPos_ID LifeCodeLifeTotal
4A  2015-08-31 00:00:00.000 OO-NSN  45023   1   LCF1    68.0000
4A  2015-08-31 00:00:00.000 OO-NSN  45023   2   LCF1    67.0000

becomes this:

JourneyNumber   JourneyDate Reg      ID LifeCode    ALCF1   BLCF1
4A  2015-08-31 00:00:00.000 OO-NSN  45023   LCF1    68.0000 67.000

but instead I'm getting this:

JourneyNumber   JourneyDate    Reg       ID     LifeCode        ALCF1   BLCF1
4A  2015-08-31 00:00:00.000      OO-NSN 45023      LCF1         68.0000 NULL
4A  2015-08-31 00:00:00.000      OO-NSN 45023      LCF1         NULL    67.0000

Can anyone help me figure this out?

Thanks!

score 0 · Answer 1 · answered Dec 10 '19 at 10:53

use aggregation

SELECT TOP (1000) 
  a.[JourneyNumber]
  ,a.[JourneyDate]
  ,a.[tReg_ID]
  ,a.[Reg]
  ,a.[ID]
  ,b.tLocationPosition_ID
  ,c.LifeCode
  ,c.LifeTotal

  ,max(CASE WHEN c.LifeCode LIKE 'LCF1' AND b.tLocationPosition_ID = 1 THEN c.LifeTotal END) as ALCF1
  ,max(CASE WHEN c.LifeCode LIKE 'LCF1' AND b.tLocationPosition_ID = 2 THEN c.LifeTotal END) as BLCF1
FROM [RALNHVTST].[dbo].[tRegJourney] as a

LEFT JOIN [RALNHVTST].[dbo].[tRegJourneyLogBook] as b
ON a.ID = b.tRegJourney_ID

LEFT JOIN [RALNHVTST].[dbo].[tRegJourneyLogBookLifeCodeEvents] AS c
ON b.ID = c.tRegJourneyLogBook_ID

WHERE b.tLocation_ID = 720
AND a.tReg_ID = 73 OR a.tReg_ID = 38
AND (b.tLocationPosition_ID = 1 OR b.tLocationPosition_ID = 2)
AND (c.LifeCode LIKE 'LCF1' )
group by  a.[JourneyNumber]
  ,a.[JourneyDate]
  ,a.[tReg_ID]
  ,a.[Reg]
  ,a.[ID]
  ,b.tLocationPosition_ID
  ,c.LifeCode
  ,c.LifeTotal
ORDER BY JourneyDate

That's the first thing I considered, but since I use b.tLocationPosition_ID to define my columns it is not an option. The solution you posted ives me the exact same result as without GROUP BY. — Cathal Clavie, Dec 10 '19 at 10:56
@CathalClavie could you provide https://dbfiddle.uk/ table with data in the fiddle — Zaynul Abadin Tuhin, Dec 10 '19 at 11:03

score 0 · Accepted Answer · answered Dec 10 '19 at 11:42

Fix your query so the table aliases are meaningful!

I also doubt that your WHERE clause is really doing what you want. In the query below, I have slightly changed the conditions, so the conditions on the second table apply to all rows in the result.

For performance, I would recommend CROSS APPLY:

SELECT TOP (1000) rj.[JourneyNumber], rj.[JourneyDate]
       rj.[tReg_ID], rj.[Reg], rj.[ID]
       rjlb.tLocationPosition_ID,
.LifeCode,
       ce.ALCF1, ce.BLCF1
FROM [RALNHVTST].[dbo].[tRegJourney] rj
      CROSS APPLY
     (SELECT SUM(CASE WHEN rjlb.tLocationPosition_ID = 1 THEN ce.LifeTotal END) as ALCF1,
             SUM(CASE WHEN rjlb.tLocationPosition_ID = 2 THEN ce.LifeTotal END) as BLCF1
      FROM [RALNHVTST].[dbo].[tRegJourneyLogBook] rjlb
           [RALNHVTST].[dbo]. tRegJourneyLogBookLifeCodeEvents ce
           ON rjlb.ID = ce.tRegJourneyLogBook_ID AND
      WHERE rj.ID = rjlb.tRegJourney_ID AND
            rjlb.tLocationPosition_ID IN (1, 2) AND
            rjlb.tLocation_ID = 720 AND
            ce.LifeCode LIKE 'LCF1'
     ) ce
WHERE rj.tReg_ID IN (73, 38 
ORDER BY JourneyDate ;

getting rid of NULL rows when transforming a row into a column

2 Answers2