rickyProgrammer rickyProgrammer - 3 months ago 13
SQL Question

SQL Query to Group Values Accordingly and Distictly

Good day experts,

I have a raw data in database which look something to this (simplified). Table name: tblSchedule

Employee Date Tenants
Employee 1 1-Jan-16 McDonalds
Employee 1 1-Jan-16 Burger King
Employee 1 2-Jan-16 Jamba Juice
Employee 2 2-Jan-16 Kenny Rogers
Employee 2 3-Jan-16 Starbucks


What I need to do is group the data accordingly per Employee and have a date columns based on the available record. I have started using this Query in SQL (I have used of a static date, but in my current code, the dates are being loaded dynamically based on user input)

Select Employee, Date, Tenant
Into #Query1
From tblSchedule


Select Employee,
CASE WHEN (Date= '01/01/2016') THEN Tenant ELSE Null END AS [01/01/2016]
,CASE WHEN (Date= '01/01/2016') THEN Tenant ELSE Null END AS [01/02/2016]
,CASE WHEN (Date = '01/03/2016') THEN Tenant ELSE Null END AS [01/03/2016]
From #Query1


And it produces this kind of Output

Employee 1-Jan-16 2-Jan-16 3-Jan-16
Employee 1 Mcdonalds null null
Burger King null null
null Jamba Juice null
Employee 2 null Kenny Rogers null
null null Starbucks


What I need to achieve as an output is proper grouping where in null values are removed like this

Employee 1-Jan-16 2-Jan-16 3-Jan-16
Employee 1 Mcdonalds Jamba Juice null
Burger King null null
Employee 2 null Kenny Rogers Starbucks


I have come to this code but still unable to produce the desired output

Select Employee,
CASE WHEN (Date= '01/01/2016') THEN Tenant ELSE Null END AS [01/01/2016]
,CASE WHEN (Date= '01/01/2016') THEN Tenant ELSE Null END AS [01/02/2016]
,CASE WHEN (Date = '01/03/2016') THEN Tenant ELSE Null END AS [01/03/2016]
Into #Query2
From #Query1


Select Employee,
Max[01/01/2016] as [01/01/2016],
Max[01/02/2016] as [01/02/2016],
Max[01/03/2016] as [01/03/2016],
From #Query2
Group By Employee


It is almost getting the desired output but only getting one value (max value) in 01/01/2016

Hope to get positive feedback from you. Thanks

ZLK ZLK
Answer

A simple way to do this would be conditional aggregation, using grouping by row numbers:

e.g.

SELECT Employee
     , MAX(CASE WHEN Date = '01/01/2016' THEN Tenants END)
     , MAX(CASE WHEN Date = '01/02/2016' THEN Tenants END)
     , MAX(CASE WHEN Date = '01/03/2016' THEN Tenants END)
FROM (
    SELECT *
         , ROW_NUMBER() OVER (PARTITION BY Employee, Date ORDER BY Date) RN
    FROM myTable) T
GROUP BY Employee, RN
ORDER BY Employee, RN;