1 00:02.450 --> 00:06.440 So today we are going to start the merge statement. 2 00:06.440 --> 00:08.780 Or we can say that merge command. 3 00:12.950 --> 00:17.000 Now what is the requirement of the merge command. 4 00:23.240 --> 00:31.010 So there there will be merge command is used to merge the data from a source into target. 5 00:31.010 --> 00:38.690 Applying different actions insert, update and delete based on conditional logic used to merge data 6 00:38.690 --> 00:39.860 into a table. 7 00:40.010 --> 00:42.200 So if we have. 8 00:42.920 --> 00:45.770 So here usually there are two tables. 9 00:45.800 --> 00:49.430 Source table is there and target table is there. 10 00:49.430 --> 00:50.900 Target table is there. 11 00:51.140 --> 00:52.310 So merge. 12 00:52.580 --> 01:01.810 Uh so ideally this source and target table should be sync sync with each other right So whatever data 13 01:01.810 --> 01:07.870 will be there in the source table that their same data will be available in the target table. 14 01:08.050 --> 01:15.010 So to perform this kind of operations we are usually using merge statement or merge command. 15 01:15.070 --> 01:23.470 So over here and one more important thing is there should be one common column between source table 16 01:25.450 --> 01:26.770 and target table. 17 01:26.770 --> 01:31.390 One common column should be available in both of the side. 18 01:31.630 --> 01:34.450 Now the syntax will be merged into. 19 01:34.480 --> 01:36.700 So from where we want to merge. 20 01:36.700 --> 01:39.880 So merge into target table as alias. 21 01:39.970 --> 01:42.040 So we have to give some alias. 22 01:42.040 --> 01:50.470 Any alias we can can give merge into target table as alias using using what we want to merge. 23 01:50.470 --> 01:55.300 Using source table as alias on merge condition. 24 01:55.330 --> 02:01.860 So over here there will be Common column from both the tables. 25 02:01.860 --> 02:10.620 So if, uh, over here, here alias is a and over here, here b alias is b and suppose common column 26 02:10.620 --> 02:15.210 is ID then we can mention like this a.id equal to b.id. 27 02:15.390 --> 02:17.220 This is a merge condition. 28 02:17.520 --> 02:18.450 When. 29 02:18.480 --> 02:27.690 Now there will be some possibility that some for the first time, if we are doing for the first time, 30 02:27.990 --> 02:31.230 then all the data will be fresh in the target table, right? 31 02:31.230 --> 02:32.250 All the data. 32 02:35.100 --> 02:46.200 Will be fresh in the target table because target table is empty for the first time. 33 02:46.200 --> 02:56.490 If you can see target table is empty for the first time, but now then there will be some IDs are matching 34 02:56.490 --> 02:58.440 in the source table and target table. 35 02:58.440 --> 03:01.110 So at that time, when matched. 36 03:01.140 --> 03:08.700 When matched means when this ID is matched source table ID is matching with the target table ID then 37 03:08.730 --> 03:09.420 what to do? 38 03:09.450 --> 03:11.610 Then we have to do update statement. 39 03:15.330 --> 03:17.400 Then we have to do update statement. 40 03:17.490 --> 03:22.950 Now when not match then we have to do insert. 41 03:24.720 --> 03:30.450 When not match, then we have to do the insert and when not match by source. 42 03:30.450 --> 03:34.560 We will see the example of all these three very soon. 43 03:34.560 --> 03:38.820 So when not matched by source then we have to delete. 44 03:39.360 --> 03:40.920 At that time we have to delete. 45 03:40.920 --> 03:49.500 Right now insert is used to insert records into the target if corresponding record is not present in 46 03:49.500 --> 03:50.730 the target table. 47 03:54.300 --> 03:58.070 Update is used to update the records of target as per source. 48 03:59.900 --> 04:04.970 When ID in source and. 49 04:07.760 --> 04:14.150 Target are matched right when id is source and target are matched, then and delete is used to delete 50 04:14.150 --> 04:18.410 records from the target if corresponding record is not present in the source. 51 04:19.010 --> 04:21.770 So now we will see the example. 52 04:21.800 --> 04:24.050 Then you will get better understanding. 53 04:24.380 --> 04:27.920 Now a merge statement must be terminated by a semicolon. 54 04:27.950 --> 04:29.420 This is one rule. 55 04:29.720 --> 04:33.440 A merge statement must be terminated by semicolon. 56 04:34.040 --> 04:37.070 Now let me drop this table. 57 04:40.370 --> 04:43.010 Now let me create I am creating one table. 58 04:43.010 --> 04:49.280 Create customer new table in which customer ID, customer name, phone and address columns are there, 59 04:49.280 --> 04:49.850 right? 60 04:53.120 --> 04:58.000 Now I am inserting some data into the same. 61 05:02.950 --> 05:04.390 Now let me drop. 62 05:04.630 --> 05:06.190 This is a second table. 63 05:06.220 --> 05:15.850 Customer stage, table and structure will be the same if you see this customer new and customer stage 64 05:15.850 --> 05:16.210 table. 65 05:16.240 --> 05:18.910 Both the table structure should be the same. 66 05:20.200 --> 05:27.190 Now again I am inserting some few records over here in the second table. 67 05:29.050 --> 05:34.300 Now let me take this data into Excel file. 68 05:52.150 --> 05:52.420 Okay. 69 05:52.420 --> 05:54.100 Now this is my one table. 70 05:54.100 --> 05:55.780 This is my first table. 71 05:57.520 --> 05:59.380 Let me copy the data. 72 06:05.170 --> 06:07.690 And this will become my. 73 06:09.700 --> 06:11.350 Second table. 74 06:22.690 --> 06:24.970 And let me just. 75 06:27.760 --> 06:28.060 Okay. 76 06:28.090 --> 06:29.620 This is one table. 77 06:29.920 --> 06:33.700 Table name is customer stage. 78 06:38.110 --> 06:40.330 And this is another table. 79 06:44.950 --> 06:46.330 Table name is. 80 06:51.940 --> 06:52.780 Customer. 81 06:52.780 --> 06:53.380 New 82 06:57.390 --> 07:05.610 Now, if you see over here in both the table we have customer ID column is common in both the table. 83 07:05.640 --> 07:17.280 Customer ID column is common in both the table right now over here this will become our target. 84 07:18.660 --> 07:24.360 For example this is for example only I am taking this one as a target table. 85 07:25.470 --> 07:26.970 And this one. 86 07:29.640 --> 07:31.020 Is a source table right. 87 07:31.020 --> 07:32.250 This one is a target table. 88 07:32.250 --> 07:33.810 This one is a source table. 89 07:34.920 --> 07:36.120 Now 90 07:37.830 --> 07:40.560 123 okay. 91 07:40.650 --> 07:47.400 Now if you see over here one two and three, one, two and three is common in both the table. 92 07:47.430 --> 07:52.440 Right one two and three is common in both the table one two and three. 93 07:52.620 --> 07:55.880 But now four and five is not present over here. 94 07:56.030 --> 07:59.840 Four and five is not present over here in the target table. 95 08:00.560 --> 08:11.540 And six record is additionally present in the six record is additionally present in the target table. 96 08:11.570 --> 08:20.630 So this record we have to delete because our target table should be matched with the source table. 97 08:20.870 --> 08:24.380 Target table should be exactly matched with the source table. 98 08:24.410 --> 08:26.600 So this record we need to delete. 99 08:26.600 --> 08:32.660 And these two record we need to insert these two record we need to insert. 100 08:32.690 --> 08:34.820 So this is going to be insert. 101 08:34.820 --> 08:36.560 This is going to be insert. 102 08:36.980 --> 08:39.890 And if you see SQL. 103 08:43.250 --> 08:43.730 Okay. 104 08:43.760 --> 08:46.400 And all this one two and three. 105 08:46.610 --> 08:54.130 If you see over here phone number 23454 over here 34323. 106 08:54.160 --> 08:56.350 So that is a different one right. 107 08:56.380 --> 09:01.300 So over here all these three records should be updated. 108 09:04.660 --> 09:05.890 Should be updated. 109 09:05.890 --> 09:13.030 So our final table will have record like final table will have will have record like this. 110 09:13.840 --> 09:22.480 This one these two record will be inserted and this record should be deleted. 111 09:22.510 --> 09:26.320 So this should be our final final target table. 112 09:26.710 --> 09:29.380 This should be our final target table. 113 09:29.530 --> 09:34.870 Now how we can perform this merge into customer new. 114 09:34.900 --> 09:36.310 This is my target table. 115 09:36.340 --> 09:38.620 Customer new is my target table. 116 09:40.780 --> 09:42.820 Using customer stage. 117 09:42.820 --> 09:44.230 This is my source table. 118 09:44.260 --> 09:48.460 Customer stage is my source table on TGT. 119 09:48.490 --> 09:54.450 TGT I have given alias TGT dot customer ID equal to SK. 120 09:54.660 --> 09:56.310 Customer ID when matched. 121 09:56.550 --> 09:58.260 When matched then. 122 09:58.290 --> 09:59.910 When matched then. 123 10:01.380 --> 10:02.700 Update when matched. 124 10:02.730 --> 10:06.870 Match what this ID is matching with this ID. 125 10:07.290 --> 10:09.510 This ID is matching with this ID. 126 10:09.540 --> 10:12.990 Then we have to update update with what we want to update. 127 10:13.020 --> 10:15.210 We want to update this three column right. 128 10:15.960 --> 10:18.600 We want to update this three column. 129 10:20.670 --> 10:21.540 Right. 130 10:21.900 --> 10:29.190 So this three column column going to be update set TGT dot company name equal to source dot company 131 10:29.220 --> 10:33.540 name TGT dot phone equal to source dot phone TGT dot address. 132 10:33.750 --> 10:37.830 And when not match when ID is not match. 133 10:42.450 --> 10:44.520 Not match from target to source. 134 10:44.520 --> 10:46.230 Then we need to insert this two record. 135 10:46.260 --> 10:46.830 Right. 136 10:46.890 --> 10:51.180 So at that time insert all these four for column. 137 10:51.240 --> 10:59.160 We have to mention over here for column names and values will be coming from where values will be coming 138 10:59.190 --> 11:00.750 of course from the source side. 139 11:00.750 --> 11:01.320 Right. 140 11:01.470 --> 11:08.430 That is why sk dot customer id sk dot customer name sk phone, sk dot address. 141 11:09.090 --> 11:12.810 And now we want to delete this record right. 142 11:12.840 --> 11:14.040 We want to delete this record. 143 11:14.070 --> 11:18.210 It is because it is not exist in the source. 144 11:18.510 --> 11:20.520 It is not exist in the source. 145 11:20.550 --> 11:22.320 So what is written over here? 146 11:22.380 --> 11:24.660 When not matched by source. 147 11:25.410 --> 11:29.310 When not matched by source, this record is not matching by source. 148 11:29.610 --> 11:30.330 Then what? 149 11:30.360 --> 11:31.140 Delete. 150 11:32.940 --> 11:33.570 Then what? 151 11:33.600 --> 11:34.020 Delete. 152 11:34.020 --> 11:39.030 So let me execute this statement. 153 11:39.720 --> 11:42.360 And now let me check the data. 154 11:47.730 --> 11:53.780 Select star From this select star. 155 11:53.780 --> 11:54.440 From. 156 11:57.140 --> 11:58.490 This. 157 12:04.730 --> 12:05.690 Now see? 158 12:05.690 --> 12:06.980 This is my. 159 12:09.800 --> 12:13.490 Whatever I have written over here in the explanation. 160 12:13.730 --> 12:16.790 That same thing you will able to find over here. 161 12:16.820 --> 12:18.020 Right now. 162 12:18.020 --> 12:20.810 Your source and target will be same. 163 12:21.500 --> 12:24.080 Now, this is my source. 164 12:24.560 --> 12:25.880 This is my target. 165 12:25.880 --> 12:27.320 Both will be same and above. 166 12:27.350 --> 12:28.730 I have explained you. 167 12:29.150 --> 12:31.670 That is also will be same, right? 168 12:34.850 --> 12:37.250 But now if you see over here. 169 12:38.300 --> 12:38.780 Um. 170 12:39.980 --> 12:46.520 If I run this again, let it be five rows affected, five rows affected. 171 12:46.520 --> 12:52.510 So now there is one problem over here in this statement. 172 12:54.610 --> 12:59.740 Even though if you see over here now my both the table having the same data. 173 12:59.740 --> 13:00.310 Right. 174 13:00.940 --> 13:03.460 Both the table having the same data. 175 13:04.390 --> 13:08.530 Still, still it is going to be update five rows every time. 176 13:08.560 --> 13:13.300 Still it is going to be update five rows every time. 177 13:14.620 --> 13:17.650 This is ideally not a best approach right. 178 13:17.980 --> 13:22.600 If we have 5 million rows then also it is going to be update. 179 13:23.800 --> 13:31.600 If we have ten lakhs rows then also it is going to be update matching by id id. 180 13:31.630 --> 13:34.450 It will update all other columns right. 181 13:34.480 --> 13:35.530 All other columns. 182 13:35.560 --> 13:38.200 So ideally this is not a right approach. 183 13:38.230 --> 13:40.090 Second approach what we can do. 184 13:42.340 --> 13:44.500 That same thing is written over here. 185 13:44.530 --> 13:50.670 The above statement updates the target table even when there is a same data available in target table. 186 13:50.850 --> 13:53.040 Update without check of same data. 187 13:53.040 --> 13:54.900 So over here even though same data. 188 13:54.930 --> 13:56.550 Are there in both the table. 189 13:56.880 --> 13:59.730 Still it is updating every time when we want. 190 13:59.760 --> 14:04.320 To update only when there is a chance in at least one of the table. 191 14:04.620 --> 14:06.360 Target table column. 192 14:06.390 --> 14:08.130 Then what we have to do. 193 14:09.150 --> 14:12.090 When matched then we are updating right? 194 14:12.120 --> 14:14.850 When match then we are updating. 195 14:14.850 --> 14:20.550 So over here we need to additionally add these three conditions. 196 14:22.620 --> 14:23.520 Additional. 197 14:29.400 --> 14:36.090 These three are additional condition when column from the source is not match with the target table, 198 14:36.450 --> 14:38.400 then any of the column. 199 14:38.400 --> 14:44.340 Not all of the column, any of the column either company name is not matching or phone is not matching, 200 14:44.340 --> 14:45.960 or address is not matching. 201 14:45.990 --> 14:50.370 If we have 200 columns, then you have to mention over here 200 column. 202 14:51.390 --> 14:54.750 Then you have to mention over here 200 column to verify. 203 14:58.260 --> 14:59.730 So this is a additional. 204 14:59.730 --> 15:04.050 So if any of this column is not matching then only it will update. 205 15:04.080 --> 15:06.330 Now my everything is matching right. 206 15:06.330 --> 15:09.600 So now let me execute this query. 207 15:10.800 --> 15:11.670 See over here. 208 15:11.670 --> 15:12.900 What is the result. 209 15:15.780 --> 15:17.490 Zero rows affected. 210 15:17.490 --> 15:19.500 And that what I want right. 211 15:19.920 --> 15:26.040 That what I want because my source and target table are same. 212 15:26.040 --> 15:28.110 Source and target table are same. 213 15:28.110 --> 15:31.170 So why I need to update every time? 214 15:31.260 --> 15:33.750 I don't need to update every time, right? 215 15:39.480 --> 15:41.010 Now one more thing. 216 15:41.040 --> 15:43.020 It one minute. 217 15:52.370 --> 15:55.220 No, no, it is not creating a fresh table. 218 15:55.250 --> 15:56.480 A fresh table. 219 15:56.510 --> 16:02.630 See, we have already created this table, right? 220 16:02.660 --> 16:04.430 We have created this table. 221 16:05.360 --> 16:08.180 Uh, this is my target. 222 16:09.080 --> 16:09.350 What? 223 16:09.350 --> 16:09.770 It is. 224 16:09.800 --> 16:10.730 One minute. 225 16:12.230 --> 16:12.530 Yeah. 226 16:12.530 --> 16:13.850 This is my target table. 227 16:13.880 --> 16:15.800 Customer new is my target table. 228 16:15.800 --> 16:17.780 I have created this table manually. 229 16:17.780 --> 16:18.410 Right? 230 16:18.890 --> 16:23.720 I have created this table manually and inserted the data into the same manually. 231 16:23.960 --> 16:26.120 Then I have created the source table. 232 16:26.120 --> 16:29.090 Also manually source table. 233 16:29.090 --> 16:33.650 Also manually I have inserted the data into the same also manually. 234 16:33.860 --> 16:35.240 These are random data. 235 16:35.270 --> 16:39.590 I have taken to explain the merge statement command right. 236 16:39.740 --> 16:44.270 So it will not created the fresh table with dbo 237 16:50.500 --> 16:51.250 I have to. 238 16:51.280 --> 16:55.060 I have to create it manually. 239 16:58.270 --> 17:05.950 Now, if you see, we have mentioned over here, update as well as insert as well as delete. 240 17:06.160 --> 17:09.880 So it is not mandatory to use all when statement. 241 17:11.800 --> 17:18.760 We can skip any condition as per our requirement like in the following code we are using when matched 242 17:18.760 --> 17:19.480 statement. 243 17:19.510 --> 17:24.130 So over here if you see when matched then update. 244 17:24.160 --> 17:28.990 Over here there is no insert and there is no delete right? 245 17:29.950 --> 17:32.530 There is no it will perform only update. 246 17:32.530 --> 17:34.360 It will perform only update. 247 17:36.220 --> 17:38.260 It will perform only update. 248 17:39.130 --> 17:42.310 It is not mandatory to have insert every time it will. 249 17:42.340 --> 17:47.790 It is not mandatory to delete every time it is based upon our requirement. 250 17:51.780 --> 17:53.760 Now you can ask me. 251 17:53.790 --> 17:55.590 I will unmute you. 252 17:55.590 --> 17:58.050 You can ask me if you have any question. 253 18:03.870 --> 18:04.170 Okay. 254 18:04.170 --> 18:05.880 Now let's see the pivot operator. 255 18:05.880 --> 18:07.560 What is a pivot operator. 256 18:08.190 --> 18:09.390 So. 257 18:11.580 --> 18:16.770 We use pivoting for cross tabulation or matrix like reports. 258 18:18.690 --> 18:22.260 Cross tabulation or matrix like like reports. 259 18:22.260 --> 18:25.800 So if you have this is my simple table is available right. 260 18:25.830 --> 18:31.350 Simple table is there in s SQL. 261 18:32.490 --> 18:39.750 And from this table I want the result like this I want the result like this. 262 18:40.530 --> 18:42.030 This is a cross. 263 18:42.060 --> 18:45.530 We can say that Cobb's cross debut lesson. 264 18:45.530 --> 18:46.130 Right? 265 18:46.190 --> 18:47.240 So. 266 18:49.490 --> 18:50.090 Over here. 267 18:50.090 --> 18:57.560 What what we are doing, we are mentioning department wise, job wise, salary department wise and job 268 18:57.590 --> 18:58.580 wise salary. 269 18:58.610 --> 18:59.990 So analytics. 270 19:00.020 --> 19:02.780 My department is analyst analytics. 271 19:02.810 --> 19:03.230 Right? 272 19:03.260 --> 19:04.130 Analytics. 273 19:04.880 --> 19:05.150 Sorry. 274 19:05.180 --> 19:08.330 Analyst now in analyst. 275 19:11.030 --> 19:14.240 Department number 20 there is a 3000 is there. 276 19:14.240 --> 19:14.480 Right. 277 19:14.480 --> 19:15.860 3000 is there. 278 19:16.640 --> 19:22.280 And in department number 20 where where is there any other analyst is there. 279 19:24.740 --> 19:25.280 Okay. 280 19:25.310 --> 19:26.600 One more analyst is there. 281 19:26.600 --> 19:30.050 If you see over here one more analyst is there. 282 19:30.050 --> 19:33.260 And over here also one more analyst is there. 283 19:33.650 --> 19:36.560 Then we have to do the summation of both of them. 284 19:36.560 --> 19:37.880 Both the salary. 285 19:37.910 --> 19:41.000 So analyst is there department number 20. 286 19:41.030 --> 19:45.260 Is there and we have to do 3000 plus 3000. 287 19:45.290 --> 19:47.930 So it is 6000. 288 19:48.140 --> 19:51.320 So this is you, we can say pivoting. 289 19:52.460 --> 19:55.640 This is this entire thing we can say as a pivoting. 290 19:55.640 --> 20:00.140 So pivoting we have to use any of the aggregate function. 291 20:04.280 --> 20:15.560 Function we have to use while doing the pivoting like sum max, min, sum max, min or any other aggregate. 292 20:15.590 --> 20:17.690 Is there count we can use? 293 20:19.550 --> 20:23.330 Now let's say we have a what? 294 20:23.360 --> 20:26.750 What is the use to convert rows into the column? 295 20:27.170 --> 20:29.300 Over here we are converting rows. 296 20:29.300 --> 20:30.920 This 20 is different. 297 20:30.920 --> 20:33.470 Rows are there right ten, 20 and 30. 298 20:33.500 --> 20:36.950 We are converting this ten, 20 and 30 as a column. 299 20:37.880 --> 20:47.080 So converting rows into a column we are using pivoting right now the syntax will be select star from. 300 20:47.770 --> 20:51.100 Then this is usually optional thing. 301 20:51.100 --> 20:58.240 In some scenarios we have to mention, whereas in some scenarios if we do not mention then also it is 302 20:58.240 --> 20:58.960 fine. 303 20:58.990 --> 21:04.210 Select star from then this is you know this is a derived table. 304 21:05.380 --> 21:09.640 This one we can say as a derived table. 305 21:09.670 --> 21:10.000 Right. 306 21:10.030 --> 21:11.620 We have seen it already. 307 21:14.530 --> 21:16.960 As alias we have given pivot. 308 21:17.050 --> 21:24.520 Now what we are doing pivot then aggregate expression for four column name in. 309 21:24.550 --> 21:33.940 So from this particular table which which row information we want to convert it into the column that 310 21:33.940 --> 21:39.520 we have to mention over here either job or department number. 311 21:39.520 --> 21:43.020 Anything whatever we want to display as a column. 312 21:43.200 --> 21:45.450 So this is all my column, right? 313 21:50.580 --> 21:52.110 And these are the row. 314 21:52.140 --> 21:53.850 These are the row right. 315 21:59.220 --> 22:04.110 Then as alias we have to gain a order by clause. 316 22:04.230 --> 22:07.320 Now job wise, department wise total salary. 317 22:07.320 --> 22:08.430 This is my requirement. 318 22:08.460 --> 22:11.040 Job wise, department wise, total salary. 319 22:11.280 --> 22:20.070 So if you see over here, if I running this query individually, then it will give me whatever I have 320 22:20.070 --> 22:26.400 mentioned over here in the Excel file that it will give department number, job and salary. 321 22:26.400 --> 22:30.540 But now I want job wise, department wise salary. 322 22:31.320 --> 22:35.370 So over here select star from this is the syntax right. 323 22:35.400 --> 22:36.300 Select star from. 324 22:36.300 --> 22:38.250 Then again I have to mention this 325 22:41.240 --> 22:44.630 statement again, I have to mention this statement. 326 22:44.660 --> 22:49.040 Select department job salary from EMP s e. 327 22:50.270 --> 22:52.550 Then we have to write pivot keyword. 328 22:52.700 --> 22:54.260 Pivot is a keyword. 329 22:54.260 --> 22:55.490 It is a operator. 330 22:55.970 --> 22:57.950 Now open bracket. 331 22:58.490 --> 23:00.740 Now I told aggregate expression. 332 23:00.740 --> 23:02.870 We have to mention what we have to do. 333 23:02.930 --> 23:05.180 We have to do some of salary. 334 23:05.210 --> 23:06.260 Sum of salary. 335 23:06.290 --> 23:06.890 Right. 336 23:06.980 --> 23:11.300 So sum of salary will be my aggregate expression. 337 23:12.740 --> 23:17.150 Now for for which columns I want to display over here. 338 23:17.390 --> 23:22.970 So for department number in department number in 2010 two and 30. 339 23:23.120 --> 23:29.600 So if I have over here Department number 40 information is there Department number 50 information. 340 23:29.600 --> 23:33.200 Is there Department number 60 information is there. 341 23:33.350 --> 23:40.670 Then there will be data of 40, 50 And 60 will also come. 342 23:40.670 --> 23:46.160 And over here also I have to mention comma 4050 comma 60. 343 23:46.190 --> 23:46.820 Right. 344 23:47.480 --> 23:51.770 But now I have only 20 and 30 Department is there. 345 23:51.770 --> 23:56.090 So that is why I have mentioned over here 20, 20 and 30. 346 23:57.590 --> 24:06.050 And after the completion order by maybe it is it is optional one if we mention then also fine. 347 24:06.050 --> 24:08.330 If we do not mention then also fine. 348 24:08.510 --> 24:14.750 Now if I execute this query it will give me the result whatever I have copied over here. 349 24:14.780 --> 24:16.310 Remove this one. 350 24:18.410 --> 24:20.630 So let me copy this one. 351 24:32.450 --> 24:35.990 So this is how my pivot operator is working. 352 24:35.990 --> 24:38.510 Now let me give you one more example. 353 24:39.050 --> 24:40.430 Now suppose. 354 24:40.820 --> 24:41.810 Now suppose. 355 24:41.840 --> 24:42.830 Same thing. 356 24:46.880 --> 24:49.070 Now suppose what is my requirement? 357 24:49.100 --> 24:55.790 Now my requirement is I want want to display job over here. 358 24:55.790 --> 24:56.570 Like this. 359 24:57.530 --> 24:58.520 Analyst. 360 24:59.540 --> 25:06.740 Then clerk, then manager, then president. 361 25:06.770 --> 25:08.960 Whatever the distinct job ID is there. 362 25:09.230 --> 25:11.330 I have to mention like this. 363 25:12.050 --> 25:18.200 Then over here we can say that department ten 2030. 364 25:18.830 --> 25:21.020 Now just I have just interchanged. 365 25:23.060 --> 25:28.070 This column into rows in this rows data into column. 366 25:28.370 --> 25:32.630 So that also I can do I have to modify my query. 367 25:32.660 --> 25:33.290 Right. 368 25:33.650 --> 25:38.880 In place of department I have to mention job in. 369 25:39.060 --> 25:43.560 So whatever I have mentioned over here that will become column automatically. 370 25:44.100 --> 25:46.470 That will become column automatically. 371 25:46.920 --> 25:48.840 So I have to mention job over here. 372 25:48.870 --> 25:52.410 Salary I have mentioned over here because I have to do aggregate right. 373 25:53.640 --> 25:55.830 I have to do a sum of salary. 374 25:55.860 --> 25:58.350 That is why salary is mentioned over here. 375 25:58.560 --> 26:01.560 And I want first column as a department. 376 26:02.730 --> 26:05.100 Now let me execute this query. 377 26:06.840 --> 26:07.890 So see. 378 26:10.770 --> 26:15.600 Whatever I have shown you now it is becoming right. 379 26:17.250 --> 26:19.920 This one and this one will be the same. 380 26:23.670 --> 26:26.550 Again I have done the pivot with some modification. 381 26:26.550 --> 26:27.150 Right. 382 26:28.140 --> 26:32.790 Now suppose let me take one more example. 383 26:33.690 --> 26:36.230 So we have EMP table is there, right? 384 26:36.260 --> 26:38.840 We have empty table is there with us. 385 26:39.980 --> 26:43.250 Select star from empty. 386 26:48.110 --> 26:53.750 Now all the employees I joining on some higher date. 387 26:53.750 --> 26:55.760 Right on some higher date. 388 26:55.940 --> 26:58.880 So I want quarter from that higher date. 389 26:58.880 --> 27:01.550 I want quarter from that higher date. 390 27:01.730 --> 27:03.620 So how can I get the quarter. 391 27:03.650 --> 27:05.720 I have to use date part right. 392 27:06.200 --> 27:08.360 Date part higher date. 393 27:09.020 --> 27:11.690 And what will be the my first argument. 394 27:11.690 --> 27:13.430 It will be a quarter right. 395 27:13.490 --> 27:15.290 Because I want quarter. 396 27:15.350 --> 27:22.670 So in the 2,001st quarter how many employees are joined. 397 27:22.700 --> 27:24.620 I want information like this. 398 27:24.650 --> 27:30.530 In 2018 first quarter in a year there will be four quarter right 1234. 399 27:30.560 --> 27:33.680 So first quarter how many many employees are joined. 400 27:33.710 --> 27:35.390 Suppose for employee. 401 27:35.390 --> 27:38.990 So I want for in 2019 first quarter. 402 27:39.020 --> 27:40.460 How many employees are joined. 403 27:40.460 --> 27:44.300 So I want suppose 5 in 2020. 404 27:44.330 --> 27:49.010 How many employees are joined in the first quarter in 2020. 405 27:49.040 --> 27:49.970 Suppose seven. 406 27:49.970 --> 27:52.760 So I want this kind of information. 407 27:54.110 --> 27:55.700 So over here. 408 27:55.730 --> 27:58.310 Select star from ten. 409 27:58.340 --> 27:59.840 Select year of higher rate. 410 27:59.840 --> 28:04.190 Because from the higher rate column I want only year. 411 28:04.730 --> 28:08.180 I don't want entire higher rate right I want only year. 412 28:08.630 --> 28:10.910 So year of higher rate is year. 413 28:11.300 --> 28:15.560 Then I have to identify the quarter right 1234. 414 28:15.560 --> 28:17.270 So this will give me quarter. 415 28:17.510 --> 28:18.710 And MP number. 416 28:18.740 --> 28:26.810 Also I want because I want to do aggregation based upon the MP number based upon the MP number. 417 28:27.500 --> 28:30.200 From MP as e pivoting. 418 28:30.200 --> 28:32.420 Now pivot count over here. 419 28:32.450 --> 28:33.950 I don't want sum. 420 28:33.950 --> 28:42.050 I want count of employee join in quarter one, quarter two, quarter three and quarter four in each 421 28:42.050 --> 28:42.710 of the year. 422 28:42.740 --> 28:47.660 So if I execute this query it will give me that result set. 423 28:47.690 --> 28:48.410 See. 424 28:57.020 --> 28:59.300 So there is a one null record. 425 28:59.300 --> 29:00.410 What is that. 426 29:02.420 --> 29:02.780 Okay. 427 29:02.810 --> 29:04.580 One record higher date is null right. 428 29:04.580 --> 29:11.330 So there is no 000 0 in 1980 1980. 429 29:11.360 --> 29:19.220 If you see 1980, only one record is there and it is 12th of December 17th of December, fourth quarter. 430 29:19.250 --> 29:19.820 Right. 431 29:19.850 --> 29:22.010 So over here, fourth quarter. 432 29:22.010 --> 29:29.930 It is coming now 1981 first quarter 1981 first quarter one record, two record. 433 29:29.960 --> 29:35.280 Now there is no record is there First quarter 1981. 434 29:35.310 --> 29:36.870 Only two records are there. 435 29:36.870 --> 29:37.680 So it is coming. 436 29:37.710 --> 29:38.640 Two records. 437 29:43.200 --> 29:44.100 Right. 438 29:57.960 --> 29:59.910 I think this is the same thing. 439 30:01.320 --> 30:07.980 Now, suppose, uh, over here I have added one additional condition. 440 30:08.130 --> 30:13.470 I have 1980, 1981, 8283 and 2022024. 441 30:13.500 --> 30:14.490 Data is there. 442 30:14.790 --> 30:21.900 But I want to see only one record which is for 1981 for all the quarters. 443 30:21.900 --> 30:24.390 So I want to see this one row only. 444 30:24.780 --> 30:30.180 So for that purpose I have to mention 19 where year is equal to 1981. 445 30:32.120 --> 30:35.870 So simply I can mention over here is here right? 446 30:39.050 --> 30:41.270 This year I can use over here. 447 30:41.270 --> 30:43.520 So it will give me only one record. 448 30:49.370 --> 30:53.360 Now if you see over here it is not mandatory. 449 30:53.840 --> 30:55.760 We can choose we we can. 450 30:55.790 --> 30:57.470 We have to choose all the columns. 451 30:57.470 --> 31:01.820 We can choose the column whatever we want to display. 452 31:02.030 --> 31:10.400 So now over here, if you see over here I have chosen all the column over here I have chosen one, two, 453 31:10.430 --> 31:11.210 three, four. 454 31:12.560 --> 31:16.010 And over here also I have chosen all the quarters. 455 31:16.010 --> 31:18.170 Select star means all the columns. 456 31:18.350 --> 31:23.480 But if I mention like this quarter one quarter two data. 457 31:23.480 --> 31:27.650 So it will give me quarter one and quarter two data only quarter three and quarter four data. 458 31:27.650 --> 31:33.020 It is not giving even though it is Available even though it is available. 459 31:33.050 --> 31:39.470 I have mentioned over here about quarter one and two, only three and four. 460 31:39.500 --> 31:45.140 I haven't mentioned, so it will give me quarter one and quarter two data only. 461 31:48.560 --> 31:53.240 Now we have to complete one more unpivot thing that will take only five minutes. 462 31:53.270 --> 31:55.580 Just wait for five minutes. 463 31:57.110 --> 32:02.600 Now suppose we have one table which is a student table. 464 32:17.660 --> 32:21.230 I am inserting the data into the same into the student table. 465 32:22.790 --> 32:25.070 Now I am doing the pivoting. 466 32:26.420 --> 32:27.770 So see over here. 467 32:27.770 --> 32:31.780 Student table select To start from. 468 32:40.840 --> 32:48.220 So if you see over here, this is my student table from the students table math, physics and chemistry 469 32:48.220 --> 32:49.900 I have taken as a column. 470 32:50.170 --> 32:52.000 Math, physics and chemistry. 471 32:52.000 --> 33:00.370 I have taken as a column and written their marks over here as it is, as it is right now. 472 33:00.400 --> 33:03.790 Reverse process Unpivot is a reverse process of pivot. 473 33:03.820 --> 33:06.820 Want to convert column into rows? 474 33:07.330 --> 33:09.790 So now for that purpose. 475 33:18.340 --> 33:20.770 One minute we have to 476 33:28.590 --> 33:35.310 Okay, so over here, what I have done over here, what I have done, I have done this. 477 33:35.490 --> 33:36.060 Uh. 478 33:37.050 --> 33:40.740 So one minute for Unpivoting. 479 33:40.770 --> 33:41.670 Let me write. 480 33:45.030 --> 33:54.900 For Unpivoting, we should have the data into the pivot form. 481 33:54.900 --> 33:57.990 We should have data into the pivot form. 482 33:58.890 --> 34:01.440 Then only we can. 483 34:03.630 --> 34:05.430 Unpivot the table. 484 34:08.220 --> 34:15.390 Otherwise, we cannot unpivot the table. 485 34:15.390 --> 34:17.790 Otherwise we cannot unpivot the table. 486 34:17.790 --> 34:21.240 So over here we need to insert this. 487 34:21.360 --> 34:27.690 That whatever the result we have got over here, we have to insert this data into somewhere, right? 488 34:27.720 --> 34:30.020 We have to insert this data into somewhere. 489 34:30.290 --> 34:36.980 So for that purpose, select star into STD five nine. 490 34:37.010 --> 34:37.940 Let me. 491 34:37.970 --> 34:40.790 This is how we are inserting the data from the. 492 34:41.060 --> 34:43.580 Let me try to insert okay. 493 34:43.610 --> 34:45.530 This rows has been inserted. 494 34:45.680 --> 34:49.130 Now I have this table is available with me. 495 34:49.820 --> 34:52.310 I have this table is available with me. 496 34:52.310 --> 34:53.480 Let me show you. 497 34:53.900 --> 34:57.830 Select star from table name. 498 34:59.300 --> 34:59.840 Okay. 499 34:59.870 --> 35:02.300 This is this is pivoted data. 500 35:02.540 --> 35:06.740 So whenever I have pivoted data then only I can do the. 501 35:08.330 --> 35:10.430 Then only I can unpivot the data. 502 35:10.460 --> 35:13.940 Otherwise normal table data I cannot unpivot right. 503 35:17.150 --> 35:19.220 Then normal unpivot data I 504 35:30.470 --> 35:32.420 Why it is giving error. 505 35:37.580 --> 35:37.910 Okay. 506 35:37.940 --> 35:38.630 Over here. 507 35:38.630 --> 35:39.350 Math. 508 35:43.040 --> 35:44.210 One minute. 509 35:47.300 --> 35:48.140 See? 510 35:48.170 --> 35:49.310 Over here. 511 35:49.850 --> 35:50.750 Whatever. 512 35:50.750 --> 35:58.130 The column from this particular table, whatever the column from this particular table that that we 513 35:58.130 --> 36:01.280 have to over here we are doing the reverse process. 514 36:01.400 --> 36:05.060 We are converting columns into the rows. 515 36:05.780 --> 36:08.330 We are converting columns into the rows. 516 36:09.320 --> 36:13.010 And in the pivot we are converting rows into the columns. 517 36:13.010 --> 36:16.580 So over here we have to mention one column. 518 36:19.490 --> 36:23.600 Whatever the column we have to mention that name we have to give over here. 519 36:23.600 --> 36:23.840 Right. 520 36:23.870 --> 36:28.120 Subject over here Unpivot will be used in marks. 521 36:28.120 --> 36:28.690 Marks? 522 36:28.690 --> 36:30.490 We want to unpivot right marks. 523 36:30.490 --> 36:33.220 We want to unpivot as well as subject. 524 36:33.250 --> 36:34.510 We want to unpivot. 525 36:34.510 --> 36:35.920 So for marks. 526 36:35.920 --> 36:40.330 Marks for subject in math, physics and chemistry. 527 36:40.390 --> 36:47.140 So whatever the distinct subject are written over here in the column that we have to mention over here. 528 36:48.130 --> 36:55.240 If there is few more subjects are there in my this table, in my this table, then I have to add that 529 36:55.240 --> 36:56.950 subject over here as well. 530 37:01.930 --> 37:02.860 Right. 531 37:09.550 --> 37:13.930 So this is how Unpivoting will be used over here. 532 37:13.960 --> 37:17.890 Aggregate functions will not be there directly. 533 37:17.920 --> 37:20.050 We have to mention the unpivot keyword. 534 37:20.890 --> 37:21.640 Right. 535 37:24.040 --> 37:26.170 Now let me know if you have any.