CODEWITHSUNDEEP
sqlsql-serversql-server-2008
rickyProgrammer
rickyProgrammer

Reputation: 1167

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

Upvotes: 0

Views: 57

Answers (1)

ZLK
ZLK

Reputation: 2884

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;

Upvotes: 2

Related Questions