SQL Query Pivot Table Rows into Columns

Question

I have data like this which I want to create matrix result like rows into column.

Seq JobNo       TrnDate     TrnHour
1   J-000198    2016-04-02  5
2   J-000198    2016-04-03  4
3   J-000198    2016-04-04  8
4   J-000198    2016-04-05  7
5   J-000198    2016-04-06  2
6   J-000198    2016-04-07  3
7   J-000198    2016-04-08  6

On 2016-04-02 TrnDate the day was Saturday and so on. I want result like this through sql query and populate in gridview to add/edit by a user.

Saturday Sunday Monday Tuesday Wednesday Thursday Friday
02       03     04     05      06        07       08
5        4      8      7       2         3        6

Is this possible? Please help. Thanks

Edited---------

Its a complete month entries for each JobNo. It makes sum for each day. For example if I add one more row like this it make sum for Saturday date 9+2=11 and hours 5+5=10. I want it to be separate for each week.

Seq JobNo       TrnDate     TrnHour
8   J-000198    2016-04-09  5

Giorgos Betsos · Accepted Answer · 2016-04-05 10:32:04Z

2

Try this:

SELECT col, [Saturday], [Sunday], [Monday], [Tuesday], 
       [Wednesday], [Thursday], [Friday]
FROM (
  SELECT JobNo, x.col, x.val,
         DATENAME(WEEKDAY, TrnDate) AS d
  FROM mytable
  CROSS APPLY (
     VALUES ('Hour', TrnHour), 
            ('Day', DATEPART(dd, TrnDate))) x(col, val)  
) AS src
PIVOT (SUM(val) FOR d IN ([Saturday], [Sunday], [Monday], [Tuesday], 
                          [Wednesday], [Thursday], [Friday])) AS pvt

Demo here

To account for different weeks you have to include the week number in the SELECT clause of the query used to generate the source table of the PIVOT operation:

SELECT col, w, [Saturday], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday]
FROM (
  SELECT JobNo, x.col, x.val, 
         DATEPART(WEEK, TrnDate) AS w,
         DATENAME(WEEKDAY, TrnDate) AS d
  FROM mytable
  CROSS APPLY (
     VALUES ('Hour', TrnHour), ('Day', DATEPART(dd, TrnDate))) x(col, val)   
  ) AS t
PIVOT (SUM(val) FOR d IN ([Saturday], [Sunday], [Monday], [Tuesday], 
                              [Wednesday], [Thursday], [Friday])) AS pvt
ORDER BY w, col

Demo here

edited Apr 5, 2016 at 10:32

answered Apr 5, 2016 at 8:22

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raja Over a year ago

Thank you for reply Giorgos Betsos. I edited my post. I have several rows means complete month rows. I want to keep seperate result for each week but it summarize the values

Giorgos Betsos Over a year ago

@Raja You can easily modify the above query in order to account for separate weeks. Just add the weeknumber in the SELECT clause of the src derived table.

Raja Over a year ago

I couldnt do this. I get week number like this datepart(day, datediff(day, 0, TrnDate)/7 * 7)/7 + 1 but dont know how to put into your query

Husen · Accepted Answer · 2016-04-05 09:45:21Z

0

You can do like this.

SELECT info, [Saturday], [Sunday], [Monday], [Tuesday],[Wednesday],[Thursday], [Friday]
FROM (
    SELECT 'Total Hour' as info, JobNo, trnHour,DATENAME(WEEKDAY, TrnDate) AS d FROM JobTable   
    union all
    SELECT 'Date' info,JobNo, datepart(day,TrnDate) as trnHour,DATENAME(WEEKDAY,TrnDate) AS d FROM JobTable 
) AS src
PIVOT (SUM(trnHour) FOR d IN ([Saturday], [Sunday], [Monday], [Tuesday],[Wednesday], [Thursday], [Friday])) AS pvt

Week wise.

SELECT [week],info, [Saturday], [Sunday], [Monday], [Tuesday],[Wednesday],[Thursday], [Friday]
FROM (
    SELECT 'Total Hour' as info, JobNo, trnHour,DATENAME(WEEKDAY, TrnDate) AS d,datepart(week,TrnDate) [week] FROM JobTable 
    union all
    SELECT 'Date' info,JobNo, datepart(day,TrnDate) as trnHour,DATENAME(WEEKDAY,TrnDate) AS d,datepart(week,TrnDate) [week] FROM JobTable   
) AS src
PIVOT (SUM(trnHour) FOR d IN ([Saturday], [Sunday], [Monday], [Tuesday],[Wednesday], [Thursday], [Friday])) AS pvt

edited Apr 5, 2016 at 9:45

answered Apr 5, 2016 at 9:04

Husen

1,58510 silver badges14 bronze badges

4 Comments

smoore4 Over a year ago

Missing a bracket on [Wednesday]. FYI

Husen Over a year ago

@Raja query return what u want thats it.

Raja Over a year ago

@saddam_msp Its not what I want to be returned. I mentioned above what is the requirement. Please go through it if you willing to reply. Thanks

Husen Over a year ago

hey @Raja please check i have edited for week wise as per you hv axpected.

Collectives™ on Stack Overflow

SQL Query Pivot Table Rows into Columns

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related