Ruhaan Ruhaan - 5 months ago 13
SQL Question

Need Following Output Using Pivot

I have column Names as

TimeStart Scanner


I want following output using pivot operations.please help me to achieve the following.

TimeStart Hour Scanner1 Scanner2 Scanner3 Scanner4 Total
00:00 1 8000 9425 7200 8400 33026
01:00 2 11000 10800 7700 8100 37602
02:00 3 6850 11450 7940 7900 34143
03:00 4 7200 600 9425 7700 24929
04:00 5 7900 0 10800 8650 27355
05:00 6 8120 0 11450 8800 28376
06:00 7 8450 0 10800 9275 28532
07:00 8 7850 0 10700 9285 27843
08:00 9 9690 0 9500 8400 27599
09:00 10 10400 0 8100 8150 26660
10:00 11 9420 0 7740 7740 24911
11:00 12 9850 0 7650 7650 25162
12:00 13 7475 1500 6500 6500 21988
13:00 14 8400 4800 9750 9750 32714
14:00 15 8200 9850 7740 8800 34605
15:00 16 9425 9500 7650 8750 35341
16:00 17 10800 8100 6500 9425 34842
17:00 18 11450 7740 9750 10800 39758
18:00 19 10800 6984 8700 11450 37953
19:00 20 10700 7420 7740 10800 36680
20:00 21 9500 6000 7650 10700 33871
21:00 22 8100 4800 6500 9500 28922
22:00 23 7740 4200 9750 8100 29813
23:00 24 7650 600 11450 7740 27464

Answer

You can also do this using conditional aggregation with CASE EXPRESSION and ROW_NUMBER():

SELECT t.timeStart,
       MAX(CASE WHEN t.rnk = 1 THEN t.scanner END) as scanner1,
       MAX(CASE WHEN t.rnk = 2 THEN t.scanner END) as scanner2,
       MAX(CASE WHEN t.rnk = 3 THEN t.scanner END) as scanner3,
       MAX(CASE WHEN t.rnk = 4 THEN t.scanner END) as scanner4,
       SUM(t.scanner as total_scanner
FROM(
    SELECT s.*,
           ROW_NUMBER() OVER(PARTITION BY s.TimeStart ORDER BY s.scanner) as rnk
    FROM YourTable s) t