Display Column as Rows (Transpose)

Hi,
I have a query like below which returns a single row and 5 columns. I would like to display it 5 rows in a single column. Basically switch columns to rows.
SELECT
round(sum(case when Column1 = ‘ABC’ then 1 0 end)) AS “CL1”,
round(sum(case when Column2 = ‘XYZ’ then 1 else 0 end)) AS “CL2”,
round(sum(case when Column3 = ‘PQR’ then 1 else 0 end)) AS “CL3”,
round(sum(case when Column4 = ‘EFG’ then 1 else 0 end)) AS “CL4”,
round(sum(case when Column5 = ‘HIJ’ then 1 else 0 end)) AS “CL5”,
FROM mytable
where {{Filter1}} and {{filter2}} and {{filter3}}

1 Like

I have similar issue, anyone know a solution?

Hi,
I could find round about way to do it.

  1. Write a custom SQL Query using Case statement. If you are using filters, then it becomes nightmare
  2. Create KPI for each and add it to dashboard. On the dashboard click on +add and keep adding. It will start building a bar chart for each KPI side by side. This felt better approach. Hope this helps. If not then publish your table and I will give you the exact method

I’m having a similar issue, but I’m new here. Can you tell me what a KPI is and how to create one? I can’t find it in any of the documentation. I’m hoping once I understand that, the rest of your solution will make sense to me and I’ll be able to use it.

Hi Amy

KPI is basically visualization type of Number. example
image

Each of the items which I needed to display as column, i created a KPI. In the dashboard when you hover ur mouse on the KPI, it shows +Add option. I added all the KPIs and it started displayed each KPI as a bar chart.

This is not exactly switching rows to columns but a alternate method. If you want tabular form, then you will need to use a custom query option depending on the source database type.

Take a look at this thread as example for SQL server