Pivot without repeated rows

lthor · February 5, 2025, 1:11pm

Hi, i’m using Grafana 10.2.3

I’ve already built the required PIVOT table with Grouping To Matrix Transformation.

This transformation use one row. So i had to concat 3 fields togheter and then i have a lot of YYYY-MM columns with values.

Should be all ok, but the customer does not want repeated rows
so that FROM this template example

AAAA 1111 item1 5 …
AAAA 1111 item2 6…
AAAA 2222 item1 10…
AAAA 2222 item3 9…
BBBB 1111 item2 12
BBBB 1111 item4 15
BBBB 3333 item6 18

i HAVE TO SHOW THIS target template

AAAA 1111 item1 5 …
item2 6…
AAAA 2222 item1 10…
item3 9…
BBBB 1111 item2 12
item4 15
3333 item6 18

Do you have an idea on which kind of transformations i have to use to arrive to this result?

Looking forward to hear from you
THANK YOU!! the Grafana Team

Luigi

PS
I try to load an excel template from/to example

joachimschiewek · February 5, 2025, 2:16pm

Are you able to use the “business table” plugin?
There you can group columns.

lthor · February 5, 2025, 2:19pm

Thanks joachimschiewek
I 've installed the plugin i did the grouping, so the tree view works, but
it seems that cannot transform in a pivot table with a grouping to matrix transformation. With a grouping to matrix transformation no data appears. With a normal table it is OK.
And after the export in csv the rows are not in a tree view format

yosiasz · February 5, 2025, 4:18pm

what is the datasource? please provide sample data the way it is before pivot transformation not as an image but as

col1,col2,col3,col4
333,AAA,item.juice

type if out here as above

lthor · February 5, 2025, 4:32pm

Hi yosiasz, i’m using mysql

I need to make this.

FROM this standard format

name, place,item,period, quantity
NAME1,AREA1,ITEMA,2025-01,10
NAME1,AREA1,ITEMB,2025-01,10
NAME1,AREA2,ITEMC,2025-01,5
NAME1,AREA2,ITEMD,2025-01,6
NAME2,AREA1,ITEMA,2025-01,7
NAME2,AREA2,ITEMB,2025-01,8
NAME2,AREA2,ITEMC,2025-01,1
NAME3,AREA1,ITEMA,2025-01,1
NAME1,AREA1,ITEMA,2025-02,100

TO this tree view mode + pivot

name, place,item, 2025-01,2025-02
NAME1,AREA1,ITEMA,10,100
     ,     ,ITEMB,10,
     ,AREA2,ITEMC,5,
     ,     ,ITEMD,6,
NAME2,AREA1,ITEMA,7,
     ,AREA2,ITEMB,8,
     ,     ,ITEMC,1,
NAME3,AREA1,ITEMA,1,

yosiasz · February 5, 2025, 4:40pm

nice. now please explain how one item is related to another in the treeview. what is the logic behing that treeview?

lthor · February 5, 2025, 4:44pm

it is a hierarchy view
NAME1 has some areas
NAME2 has some areas

the items are the products that are bougth in quantity x in the y area for a period in YYYY-MM format. Name,Place,Item is a Unique Key
I already did the pivot with Group to matrix Transformation with a concat field (name,place, item) BUT the customer wants NO repeated rows (like Grouped field in Business table).

I’ve tried also TreeView but it seems is not possibile to have a join between BusinessTable/TreeView plugin and pivot (trough Group to Matrix Transformation)

In a TreeView panel it seems is impossibile to have the values on YYYY-MM columns as leafs at the same level.

With a Business Table it seems the problem of pivoting is the same.

yosiasz · February 6, 2025, 2:46pm

so for example this is me doing it in ms sql without transformation because I prefer to control things and leave grafana at what it does best: visualize

select name,
		place,
		item,
		[2025-01], [2025-02]
from (
		select 'NAME1' as name,'AREA1' as place,'ITEMA' as item,'2025-01' as period,10 quantity union
		select 'NAME1','AREA1','ITEMB','2025-01',10 union
		select 'NAME1','AREA2','ITEMC','2025-01',5 union
		select 'NAME1','AREA2','ITEMD','2025-01',6 union
		select 'NAME2','AREA1','ITEMA','2025-01',7 union
		select 'NAME2','AREA2','ITEMB','2025-01',8 union
		select 'NAME2','AREA2','ITEMC','2025-01',1 union
		select 'NAME3','AREA1','ITEMA','2025-01',1 union
		select 'NAME1','AREA1','ITEMA','2025-02',100
) a
PIVOT  
(  
  sum(quantity)  
  FOR period IN ([2025-01], [2025-02], [RD2127], [RD2139])  
) AS PivotTable
order by name, place, item

then I can loop through this above result in mssql and create the tree view needed. you will have to do it in mysql the same. the feature you want does not exist in grafana. prepare the data the way you want it to look outside of grafana then simply visualize it in grafana

lthor · February 6, 2025, 6:25pm

Thank you yosiasz.

I’ve tried to build a view that already has data in pivoting format, also with static columns in YYYY-MM format, but quering on it trough TreeView or BusinessTable return me an error “db query error: query failed - please inspect Grafana server log for details”
Very strange same fileds on other tables works. On pivot view NO.

yosiasz · February 6, 2025, 6:40pm

can you share what the query looks like for the given data and also what pivot you used and are are you using this below TreeView plugin?

lthor · February 6, 2025, 7:14pm

I try to create this view in static mode to test last months on mysql
CREATE OR REPLACE VIEW pivotview AS
SELECT
name,
area,
item,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2024-09’ THEN qnt ELSE 0 END) AS 2024-09,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2024-10’ THEN qnt ELSE 0 END) AS 2024-10,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2024-11’ THEN qnt ELSE 0 END) AS 2024-11,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2024-12’ THEN qnt ELSE 0 END) AS 2024-12,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2025-01’ THEN qnt ELSE 0 END) AS 2025-01,
SUM(CASE WHEN DATE_FORMAT(dataperiodo, “%Y-%m”) = ‘2025-02’ THEN qnt ELSE 0 END) AS 2025-02
FROM
salesv
GROUP BY
name, area, item;
cannot understand why i get the error “db query error: query failed - please inspect Grafana server log for details” on this view.

No error on same fields and other tables.
Anyway should be VERY USEFUL if TreeView panels shoud have leafs fields for indicators like min,max,avg,count, total…and also YYYY-MM pivoting fields. I think that the result should be great !

What i would like to do is
Name1
…Area1.1
…ItemA 2025-01 2025-02 …ecc

i’ve tried to delete the grouping fields (SUM) and it works but with ‘YYYY-MM’ fields return error.

yosiasz · February 6, 2025, 7:48pm

you have provided the same issue you have already described but failed to answer the last question asked of you

what pivot did you use within grafana or are you using pivot in mysql? re are you using the TeeView plugin I shared image of?

lthor · February 6, 2025, 7:54pm

Excuse me, i’ve tried to avoid pivoting inside grafana. I’m on Grafana 10.2.3 i simply wrote this query
select name,area,item from pivotview (see ddl above) with TreeView panel and i get the error

with ${name}
${area}
${item}

yosiasz · February 6, 2025, 10:31pm

send a screen shot of your dashboard showing the query panel and the treeview tree level definitions.

Using Business Text and html to layout the table, then clean up duplicate cells using javascript.

lthor · February 7, 2025, 12:51am

WOW GREAT !!!
And it is exportable in excel in the same format?

The same query from HeidiSQL works.

yosiasz · February 7, 2025, 1:55am

It seems like your requirements change with every post. I am not sure what you are asking now

lthor · February 7, 2025, 6:17am

Informatore is Name and microarea is Area
My requirements are the same and you did. This is a great workaround. I only asked if your layout can be exported in excel in the same format or not.

May i see what you did? do you used a pivoted data or a not pivoted data?

yosiasz · February 7, 2025, 5:21pm

Yes I used pivot like you did like in the view. But now you have a third sample data from a different table and not pivoted, and there is an error at bottom as you can see.

lthor · February 8, 2025, 9:47am

The error i get is on pivoted data. i do not like pivoted data because in the sample i wrote it is static , so i have to change it every month, it seems slower than transform Grouping to Matrix and, cannot understand why, i get an error only in Grafana. The queries on static fields like 2024_07 works on heidisql not in Grafana.
The solution i wrote works on this query with not pivoted data + Grouping to Matrix transformation but there is no way to hide repeated rows. I didn’t find a transform solution that could be useful because such transformed data are also present in csv export.
SELECT
DATE_FORMAT(dataperiod, ‘%Y-%m’) vista,
sum(qnt) Sales,
concat(name,‘-’,area,‘-’,item) detail
from salesv
where $__timeFilter(dataperiod)
group by
DATE_FORMAT(dataperiod, ‘%Y-%m’),
concat(name,‘-’,area,‘-’,item)
having sum(qnt) >0

Topic		Replies	Views
Lost values after Grouping to Matrix Grafana transformations	6	757	July 15, 2024
Creating a pivot table in Grafana Table Panel	5	1600	July 25, 2024
How to transform row to field and keep first column? Dashboards transformations	6	3634	August 25, 2023
Question on transformation (Grouping to matrix) Dashboards transformations	8	399	February 20, 2025
What's the best way to pivot data Dashboards	1	10484	September 27, 2022

Pivot without repeated rows

Related topics