How to stack data vertically in a list report column

[Mike G]

One of my customers is asking for the following:

They want the data in one of the database tables to be stacked vertically in a list report. The database table screen shot is pic1.jpg and as you can see the hts codes are in column/horizontal fashion with a lot of NULL values displayed.

The way they want the data displayed in the report is in pic2.jpg, where the item is listed with the hts codes stacked vertically.

The problem is this... they don't want to see the NULL/blank values. There are 11 possible hts codes and many of them will be NULL. They don't want to see 11 lines in the excel export for item number 10009000, with 1 row of data and 10 blank/NULL rows under it. They only want to see 1 row for item 10009000.

Same for item number 20002410. They only want to see the 5 hts codes in the column in excel, not the 6 blank extra values.

I've tried numerous ways to get this to work...

1) I tried creating variables in the report for the NULL values, then inserted a table in the hts code column of the list report with singletons for each hts code. I added a render variable on the singleton and added a style variable to the table row (box type = None) so the NULL values wouldn't show... that didn't work.
2) I created some custom SQL using a CTE and filtered out the NULL values in the final select statement and joined that custom SQL query to the main query by item number. That got me where I needed to be with stacking the hts codes vertically and not displaying the NULL records, BUT, I ran into an issue where the counts/amounts in the report duplicated because any item number with more than 1 hts code would produce more than 1 item in the join, causing the duplication.

Is what my customer wants possible? If so, how? Thank you!

[dougp]

Query:  Query1
data items:
[itemnumber]
[htscode00]
[htscode01]
[htscode02]
[htscode03]
[htscode04]
[htscode05]
[htscode06]
[htscode07]
[htscode08]
[htscode09]
[htscode10]


Query:  Query2
data items:
[itemnumber]


Query:  num
SQL =

Code Select Expand

with
a as (
  select 0 as num
  union all
  select num + 1
  from a
  where num < 10
)
select *
from a(or equivalent for your RDBMS)
data items:
[num]


Query:  Query3
Query2 inner join num on 1=1
data items:
[itemnumber]
[num]


Query:  Query4
Query3 inner join Query1 on itemnumber = itemnumber
data items:
[itemnumber]
[num]
[htscode] =

Code Select Expand

case [num]
  when 0 then [htscode00]
  ...
  when 10 then [htscode10]
endfilter:  [htscode] is not null

[Mike G]

@dougp thank you for your response. I'll try and get this working.