How to show multiple column in a table with table type as time series?

Question

Below is the table showing data, here what I want is to show name and leave type as a separate column so is this possible ? if please guide

Below is the format in grafana for representing time series data in table format .

SELECT il.DateFrom as time, // this is required for time series
       il.NoOfDays,// for calculation of value
       ie.FirstName +' '+ie.LastName +' ,'+' '+(( select name from IMS_LookupDetail where id=ic.TypeCode) ) as metric // for metric column 
FROM IMS_LeaveManagement il JOIN
     IMS_Employee ie 
     ON il.EmpId = ie.Id JOIN
     IMS_LeaveConfiguration ic 
     ON ic.Id = il.LeaveConfigurationId JOIN
     IMS_LookupDetail id 
     ON id.Id = ic.TypeCode or id.Code = ie.DepartmentCode
 WHERE $__timeFilter(il.DateFrom) AND
       il.Status in ('1','2') and id.name in ($dept)

Here i am not able to display more then three column in the table , what want is to show leave details as a separate column but in time series i don't know how to do so

Possible duplicate of [sql server split comma separated values into columns](https://stackoverflow.com/questions/39394299/sql-server-split-comma-separated-values-into-columns) — DhruvJoshi, Jun 18 '18 at 07:36

score 1 · Answer 1 · edited Jun 18 '18 at 08:05

1

select 
   fromdate,
   employeename,
   noofleaves,       
   substring(employeename,1,charindex(',',employeename)-2) as empname,        
   substring(employeename,(charindex(',',employeename))+1,(len(employeename)-(charindex(',',employeename)))) as leavetype
from tablename

edited Jun 18 '18 at 08:05

DhruvJoshi

17,041
6
41
60

answered Jun 18 '18 at 07:46

bindu madhavi

54
8

score 0 · Answer 2 · answered Jun 18 '18 at 07:42

if your leave type appearing in the employee name is fixed and limited, you can simply do this

SELECT REPLACE(
       REPLACE(EMPLOYEE_NAME, ',Earned Leave', ''), 
                              ',Casual Leave', '') AS EMPLOYEE_NAME,
       CASE WHEN EMPLOYEE_NAME LIKE '%Earned Leave%' THEN 'Earned Leave'
            WHEN EMPLOYEE_NAME LIKE '%Casual Leave%' THEN 'Casual Leave'
            ELSE 'Unknown Leave'
            END AS LeaveType

How to show multiple column in a table with table type as time series?

2 Answers2