CODEWITHSUNDEEP
sqlsql-servert-sqlselectpivot
Tiju John
Tiju John

Reputation: 1180

t-sql group by pivot query

I have a result set 

name        time_begin      time_end
SUNDAY      00:01:00        13:59:00
SUNDAY      14:01:00        23:59:00
MONDAY      22:01:00        21:59:00
TUESDAY     00:01:00        23:59:00
WEDNESDAY   00:01:00        23:59:00
THURSDAY    00:01:00        23:59:00
FRIDAY      00:01:00        23:59:00
SATURDAY    00:01:00        23:59:00

now I want this data to be shown as 

SUNDAY_begin    SUNDAY_end      MONDAY_begin    MONDAY_end  TUESDAY_begin   TUESDAY_end
00:01:00        13:59:00        22:01:00        21:59:00    00:01:00        23:59:00
14:01:00        23:59:00        null            null        null            null

and so on for other days.

how could I achieve this in t-sql query?

UPDATE: -> @Deepak's query: yes. since there are initially two rows in sunday, so the second row in the new resultset. since there are no multiple rows for Monday or Tuesday, it is null. there is no logical grouping. Imagine its just a list of possible time slots for any day. Thanks.

Upvotes: 0

Views: 82

Answers (2)

Christian Barron
Christian Barron

Reputation: 2755

This should give you the results you are looking for:

Select t1.Sunday Sunday_Begin, t2.Sunday Sunday_End, t1.Monday Monday_Begin, t2.Monday Monday_End, t1.Tuesday Tuesday_Begin, t2.Tuesday Tuesday_End, t1.Wednesday Wednesday_Begin, t2.Wednesday Wednesday_End, t1.Thursday Thursday_Begin, t2.Thursday Thursday_End, t1.Friday Friday_Begin, t2.Friday Friday_End, t1.Saturday Saturday_Begin, t2.Saturday Saturday_End

From

(Select RNo, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
from
(Select ROW_NUMBER() Over(Partition by name order by time_begin) RNo, name, time_begin
from #tbl) tb1
pivot
(min(time_begin) for name in (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)) as pv1) as t1

JOIN

(Select RNo, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
from
(Select ROW_NUMBER() Over(Partition by Name order by time_begin) RNo, name, time_end
from #tbl) tb2
pivot
(min(time_end) for name in (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)) as pv1) as t2 on t1.RNo = t2.RNo

Upvotes: 0

Sarath Subramanian
Sarath Subramanian

Reputation: 21301

You need to get a column for ROW_NUMBER() to get rows for repeated values in each weekdays.

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY (SELECT 0)) RNO,
    NAME+'_begin' [DATECOL],time_begin
    FROM yourtable
    UNION ALL
    SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY (SELECT 0)) RNO,
    NAME+'_end',time_end
    FROM yourtable
)
SELECT 
MIN(CASE WHEN [DATECOL]='SUNDAY_begin' THEN time_begin END)SUNDAY_begin,
MIN(CASE WHEN [DATECOL]='SUNDAY_end' THEN time_begin END)SUNDAY_begin,
MIN(CASE WHEN [DATECOL]='MONDAY_begin' THEN time_begin END)MONDAY_begin,
MIN(CASE WHEN [DATECOL]='MONDAY_end' THEN time_begin END)MONDAY_end,
MIN(CASE WHEN [DATECOL]='TUESDAY_begin' THEN time_begin END)TUESDAY_begin,
MIN(CASE WHEN [DATECOL]='TUESDAY_end' THEN time_begin END)TUESDAY_end,
MIN(CASE WHEN [DATECOL]='WEDNESDAY_begin' THEN time_begin END)WEDNESDAY_begin,
MIN(CASE WHEN [DATECOL]='WEDNESDAY_end' THEN time_begin END)WEDNESDAY_end,
MIN(CASE WHEN [DATECOL]='THURSDAY_begin' THEN time_begin END)THURSDAY_begin,
MIN(CASE WHEN [DATECOL]='THURSDAY_end' THEN time_begin END)THURSDAY_end,
MIN(CASE WHEN [DATECOL]='FRIDAY_begin' THEN time_begin END)FRIDAY_begin,
MIN(CASE WHEN [DATECOL]='FRIDAY_end' THEN time_begin END)FRIDAY_end,
MIN(CASE WHEN [DATECOL]='SATURDAY_begin' THEN time_begin END)SATURDAY_begin,
MIN(CASE WHEN [DATECOL]='SATURDAY_end' THEN time_begin END)SATURDAY_end
FROM CTE
GROUP BY RNO

RESULT

Since SQL Fiddle has internal errors, I am adding result image.

enter image description here

Upvotes: 1

Related Questions