nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Column values to row values for a chart

zax

Hi, 
 
I need some advice on how to proceed with a problem with converting a single row of data to two columns for use in a bar chart. One column containing the category and one containing the value. So the result from the data set looks a little like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
col_1_10 col_11_20 col_21_30 col_31_40 col_41_50 ....
12 45 75 81 104
</pre>
 
So to create a chart I need something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Percentage Value

10 12
20 45
30 75
40 81
50 104
...
</pre>
 
I have tried constructing a table using the above structure and then trying to use that as a chart's data source, but this does not work as the chart sees the original table bindings. 
 
This appears to be a classic example of translating data from the database world to the BIRT world. Anyone got any suggestions? 
 
Thanks in advance, 
Stephen

Find more posts tagged with

Comments

Hans_vd

You could write your query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select 10 as percentage,
col_1_10 as value
from your_table
union all
select 20 as percentage,
col_11_20 as value
from your_table
union all
...</pre>
 
Regards 
Hans

zax

Thanks, I have tried that. It works OK, but my concern is that I have ten columns that I want to make into rows, so that is ten SELECT statements... not very efficient in a growing database. 
 
Luckily I chatted to a colleague about this over lunch and he worked out a solution for me. It still requires a number of unions, but it seems fast... only 5 columns are demonstrated here: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT
md.Mobile
, Category.Name
, CASE Category.Name
WHEN 'col_1_10' THEN col_1_10
WHEN 'col_11_20' THEN col_11_20
WHEN 'col_21_30' THEN col_21_30
WHEN 'col_31_40' THEN col_31_40
WHEN 'col_41_50' THEN col_41_50
END AS col
FROM Table AS md
INNER JOIN
( SELECT 'col_1_10' AS Name
UNION SELECT 'col_11_20' AS Name
UNION SELECT 'col_21_30' AS Name
UNION SELECT 'col_31_40' AS Name
UNION SELECT 'col_41_50' AS Name
) AS Category
WHERE ....
</pre>

Hans_vd

Ah, always nice to see the smart use of a Cartesian product!