0

I am trying to combine multiple data points into one cell using coalesce and ifnull with carriage returns between the combined data points. If I remove the second coalesce statement it works, but so far no luck combining more than two sets of data. Any thoughts would be greatly appreciated. I am using SQL in DB2.

Basically I want the following results from the SQL statement:

Bob, LCE

Fred, LBD

Julie, LNJ

select 
nullif(b.column03,'') || ', ' || nullif(b.column04,'')
|| coalesce(chr(10) || nullif(b.column05,'') || ', ' || 
nullif(b.column06,''),'')
|| coalesce(chr(10) || nullif(b.column07,'') || ', ' || 
nullif(b.column08,''),'')
as "Presenter/Agency"
from  [ETL redacted] b
order by 1,2,3,4
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Andrea
  • 23
  • 4
  • Define "doesn't work". Note you're going to have problems if `column03` or `column04` are ever null. Also, you're getting rows **in text**, but not in your **result set**, which is somewhat unusual. Normally that sort of manipulation would be best left to the reporting layer. What is it you're doing here, really? – Clockwork-Muse Oct 12 '17 at 16:54
  • Solved by removing the order by statement - thanks everyone! – Andrea Oct 13 '17 at 19:26

1 Answers1

0
case when coalesce(column3,column04) > '' then
   column03  || ', ' || b.column04 
else '' end ||
case when coalesce(column05,column06) > '' then 
   case when coalesce(column03,column04) > '' then chr(10) else '' end ||
   column05  || ', ' || b.column06 
else '' end ||
case when coalesce(column07,column08) > '' then 
   case when coalesce(column03,column04,column05,column06) > '' then chr(10) else '' end ||
   column07  || ', ' || b.column08 
else '' end
Stavr00
  • 3,219
  • 1
  • 16
  • 28