# SQL Query with multiple lines

**URL:** <https://community.grafana.com/t/sql-query-with-multiple-lines/79002>\
**Category:** Dashboards\
**Created:** [January 3, 2023, 6:41pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002 "2023-01-03T18:41:43Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![antonwert](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@antonwert](https://community.grafana.com/u/antonwert)\
**Post date:** [January 3, 2023, 6:41pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/1 "2023-01-03T18:41:43Z")

</div>

Hi,

I do have a mySL/MariDB query giving me several lines with time, a value and the name of the line.  
here: [Multiple lines in one graph with only one query](https://community.grafana.com/t/multiple-lines-in-one-graph-with-only-one-query/48319)  
I found, that I have to name them time, metric and value to get several lines.  
But it does simply not work. They all are mixed together as if the metrics coloum is not present.  
Can someone help me please out, what I am missing.

This is my querry:

SELECT  
(UNIX\_TIMESTAMP(timeinfo)) AS time,  
name as metric,  
w as value  
FROM table1  
WHERE  
name in (“data1” ,“data2”, “data3” )  
and  
$\_\_timeFilter(timeinfo)  
ORDER BY timeinfo

If I enable “table view” i get this result:  
time, metric, value  
03.01.2023 19:21:03, data1, 32  
03.01.2023 19:21:03, data2, 58  
03.01.2023 19:21:03, data3, 33.4  
03.01.2023 19:23:03, data1, 30.6  
03.01.2023 19:23:03, data2, 1.6  
03.01.2023 19:23:03, data3, 9.6  
03.01.2023 19:27:08, data1, 58  
03.01.2023 19:27:08, data2, 18.3  
03.01.2023 19:27:08, data3, 48

Version: \* [v9.3.2 (21c1d14e91)]

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [January 3, 2023, 7:32pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/2 "2023-01-03T19:32:56Z")

</div>

> [@antonwert](#):
>
> But it does simply not work.

Could you please show a screen shot of “does not work” means?  
Also please post the data type of timestamp

---

<div class="post-metadata">

**Author:** ![antonwert](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@antonwert](https://community.grafana.com/u/antonwert)\
**Post date:** [January 3, 2023, 7:45pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/3 "2023-01-03T19:45:11Z")

</div>

The datatype of the SQL database is DATETIME.  
Does not work means, all data are comined into one row, but in the example I would need 3 rows (one for data1, one for data2 and a third for data3)

 ![ex1](https://us1.discourse-cdn.com/grafana/original/3X/8/a/8aa0bd49b2d298855694e003f9a8ca654df9fa2f.jpeg)

This (it is from another dashboard, with different data source and 3 independent tables) is wat i want to have in a single query:

 ![ex2](https://us1.discourse-cdn.com/grafana/original/3X/b/b/bbcfa95dd65451ce850e9d041a30c4c6ea420dfa.jpeg)

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [January 3, 2023, 7:58pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/4 "2023-01-03T19:58:11Z")

</div>

```auto
SELECT logdatetime as time, [data1], [data2], [data3]
FROM  
(
  select logdatetime, value, metric from mishmash
) AS TableToPivot 
PIVOT  
(  
  SUM(value)  
  FOR [metric] IN ( [data1], [data2], [data3])  
) AS pv; 

```

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/e/8/e8abba2198ec5d0ffaffe1be33881531b2a8f977.png)

---

<div class="post-metadata">

**Author:** ![antonwert](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@antonwert](https://community.grafana.com/u/antonwert)\
**Post date:** [January 3, 2023, 8:20pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/5 "2023-01-03T20:20:41Z")

</div>

Looks interresting, an honestly, I do not understand how it should work. It would be nice if you could explain it to me, maybe I will get it when I know how.

Do you know where the problem is with my solution?

I tried it anyway and have this result  
b query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘PIVOT ( SUM(value) FOR [metric] in (“data1” ,"data2…’ at line 15

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [January 3, 2023, 8:45pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/6 "2023-01-03T20:45:49Z")

</div>

you are welcome.

in another post you said  
“Our data is stored in a MS SQL database in long format, like (only an example):”

The above query is for MS SQL . For MariaDB you might want to go to a Mariadb forum

---

<div class="post-metadata">

**Author:** ![antonwert](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@antonwert](https://community.grafana.com/u/antonwert)\
**Post date:** [January 3, 2023, 8:48pm UTC](https://community.grafana.com/t/sql-query-with-multiple-lines/79002/7 "2023-01-03T20:48:59Z")

</div>

I’m sorry, the other post only told me the same problem. I did not focus on the SQL version in this other post.  
There, to solution looks so simple.  
I do have MySQL/MariaDB

But related to Grafana, do I understand right, that I can not use this result with 3 coloums to get 3 lines?:

time, metric, value  
03.01.2023 19:21:03, data1, 32  
03.01.2023 19:21:03, data2, 58  
03.01.2023 19:21:03, data3, 33.4  
03.01.2023 19:23:03, data1, 30.6  
03.01.2023 19:23:03, data2, 1.6  
03.01.2023 19:23:03, data3, 9.6  
03.01.2023 19:27:08, data1, 58  
03.01.2023 19:27:08, data2, 18.3  
03.01.2023 19:27:08, data3, 48