Solving SQL Query | Rows to Column in SQL

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
hey guys tawfiq here in this video let's try to solve an sql query where we will need to transform the data from row level to column level now in sql whenever we have to transform row level to column level data we generally use the concept of a pivot but not every database supports pivot for example in microsoft sql server and in oracle pivot operator is supported but when it comes to postgresql database instead of pivot they have something called as crosstab function now crosstab function kind of does the same thing that a pivot does but the syntax is slightly different and when it comes to mysql database neither pivot nor crosstab is supported but rather we can transform row level to column level data in mysql using the case statement so in this video i'm going to first showcase you an sql problem where you will need to do this kind of transformation we'll try to first analyze the input data we'll then see what the expected output is and then we will try to solve the sql problem using pivot in microsoft sql server and in oracle and then we will try to solve the same problem using crosstab function in postgresql database and finally we will see how to solve the same problem using case statement in mysql database now this problem was shared to me by one of my subscribers called winkle swinkle if you're watching this video thank you so much and anyone else if you have some interesting sql problems which you would like me to make a video about definitely share them in my email i'll leave my email id in the description below i have already got a few sql questions on my email i'm going to be covering them a pretty soon now before i can start all the data sets the sql queries the scripts that i will be writing in this video i will be sharing them in my blog as well as in my discord channel so if you want you can download them for free i'll leave the link to my blog as well as to my discord channel in the description below finally if you like my work and if you'd like to support this channel definitely consider subscribing and a like on this video and your comments definitely helps a lot in helping youtube to recommend this video to a larger audience thank you and let's begin okay so let's start by looking at the problem statement as you can see here this basically is my input table or my input data here you can see that i have three different columns it's basically the sales order table where we have the sales date and then we have the customer id and then we have the amount this basically means this is when the sale happened on which date this is the customer who purchased the sale and this is the amount of that particular sale order now our requirement is to transform this row level data into something which looks something like this okay so from row level data into column level data and when doing so there are a few different things that we need to do okay so as you can see here we need to transform this sales order data such that we need to aggregate the sales amount for each customer based on each month okay as you can see here so this is the first thing that we will need to do so the first three records here and with these all these i think 13 columns are basically the aggregation of sales amount for each customer for each month okay that's the first thing the second thing is additionally you have one more aggregation that is required which is basically the last record and this is kind of like the aggregated sales amount for each month irrespective of any customer okay so the previous data was segregation of sales amount for each customer for each month and the last record is basically the aggregation of sales amount for each month but irrespective of the customer so that is why instead of customer it's just mentioned like total okay and then finally we have another requirement here which is mentioned in the last column which kind of is the segregation or basically aggregation of the sales amount for each customer irrespective of the month okay so that is what we need to uh display here okay so it's kind of three different things that we will need to derive from this input data and then finally showcase the output in this kind of format okay and they have given a condition here telling that all the negative values so you can see here there are few negative values here these negative values when we did the transformation into the output it has been displayed with a parenthesis so all the negative values should be represented within the parenthesis okay that's also one additional thing that we will need to do okay so i hope the problem statement is clear the input data is clear and the expected output is clear now let's try to solve them and we'll try to first solve them using the microsoft sql server database now here i'm using the azure data studio tool to connect to my microsoft sql server database now what i have done is i have already loaded the input data into a table called as sales data so if i just run this you can see that this is basically my sales data i have just copied this entire input data into this table and from this table we'll try to write our sql query which will kind of give us the expected output okay so as i told you in microsoft sql server whenever you have this kind of a requirement where you need to transform data from row level into column level the concept that we can use is something called as pivot okay now in order to use the pivot operator in microsoft sql server there is kind of a different syntax for your sql query that you will need to follow and the sql query would something look like this so you just need to have select star from and inside your from class this is where you basically need to mention your base query okay this is basically your base sql query kind of like your raw data okay that is what you need to mention here and after that you need to mention your pivot operator or pivot keyword and inside the pivot keyword this is where you will need to mention couple of things the first thing is the aggregate function whatever aggregate function that you will need to use in order to do the aggregation and then finally for column values that is for whatever values are returned or basically what are the number of columns that you want to derive from this uh data right those column names you need to provide it here okay so i can just tell for column values in ah some whatever the list of columns that you will get in this particular case you can see that from this particular input data we need to come up with this data and here the different columns that we are creating is jan 21 feb 21 march 21 etc so here the value should be something like jan 21 and so on and so forth okay so this here is basically the syntax of how you can write an sql query using pivot in microsoft sql server now the first thing that we will need to do is we will need to come up with a base query of course we can use the query that we already have that is select start from sales data which is kind of having this raw data but we need to do some small transformation here in order to kind of make it ready for our aggregation okay the first thing is you can see here that we have this amount field and this amount field is basically a worker in this case and it has this dollar symbol so and we need we know that we will need to do an aggregation because you see here for customer one there are three records so when we will come up with our final output you can see here for customer one it's displaying jan has the amount as 150 right so meaning that we kind of will need to sum up this amount for this month okay you can see here this is jan 1st jan 2nd and jan 3rd of 2021 so we'll need to do this kind of aggregation so since we will need to sum up this amount first of all we will need to remove that dollar symbol okay but in our final output we still have dollar so what we will do is first we will remove this dollar symbol in our base query and then finally when when we will come up with our final output we'll try to add it back okay so that's the first transformation secondly you can see here we have the sales date with the date month and year but what we really need is we are only interested in the month and the year we are actually not bothered on which date in the month the uh order happened right so we will just transform this date field such that we only fetch the month and the year okay so let's try to do that first from our input data so i'm just going to say customer okay this is customer id and i'm going to call it like let's say customer okay and my second column would be sales date and here i need to do a transformation so i'm just going to use a function called as a format and i'm going to give the format like m m y okay y y this should basically give me the month and year okay and i'm just going to name this column as like let's say sales date and then finally i need to replace the dollar in my amount fields i'm just going to say replace this dollar with a null symbol and this will be let's say my amount field okay so i think this is my input data on my base query if i run this you can see that i am still getting the same number of 19 records just that the date has been transformed to only fetch month and year and the amount field now only displays the amount it has removed the dollar okay so first of all i'm just going to cut this and i'll just paste it inside my base query here okay so that's my first part okay so now my base query is ready there is one thing that you need to remember the thing is you cannot just put any kind of query here there are certain rules that you will need to follow when you are coming up with a base query okay the first thing is uh this base query should have three columns at least it can have more than three columns but it should definitely have at least three columns the first column in this case should basically be kind of your unique identifier for each row it's kind of your kind of like a row id okay so for each row that you will be deriving from your pivot this column here should basically kind of be like a unique id for each row okay that is what your first column should have and i can do that just by using the customer id column and the second column here should basically provide the list of categories that is this column should provide all the different values with which you will basically be creating all the columns okay so this column here that is sales date when i do this format right it should basically return me all the values which i want to get transformed into a separate column okay so i know with this kind of format it's going to already returning me jan 21 feb 21 june 21 etc right and that is exactly what i want my different columns to be right that's the second column here and then finally the third column in my base query should basically be the value that will get loaded into each of this category that is returned from the second column here okay so these are the things that you need to remember when you're coming with a base query of course i can add some additional columns here as well but those columns basically needs to be kind of like a hard coded values it has to be same for every single record okay so i can't do that but in this case i think for my particular query i think this should be pretty good enough it has all the data and everything that i need to do my pivot transformation okay so once we are ready with the base query the next thing is to do the aggregation you can see here in my base query i have not done any sum i have not done any group by because in microsoft sql server pivot we don't need to do it here we just need to provide the raw data then people inside the pivot function this is where you will need to provide your aggregation okay now i know that degradation that i will need to do here is the sum of amount okay because you can see here when so let's say i have this first three records here for the customer one for the month of jan and he has 50 50 50 orders what i need to display is for customer one for the month of jan i just need to display 150 that is the sum of these three records right some of these three amount values that is why i'm using the aggregate function as amount and the next thing is i told you whatever values will be returned from this column that is basically going to be created as a new column in my final output right so that is why here after my for keyword i just need to say that column name whatever that column name might be here okay for that column name that is sales date in you need to basically provide all the different values that will be returned from this particular column okay so in microsoft sql server instead of using single quotes i can provide these values within a square bracket so i'm just going to do that so i'm just going to create a value for each month so jan 21 and then it is feb 21 and i'm going to quickly create for all the other months okay okay so i have created basically i have provided these values for each month so that is fine now i think we are pretty good now this should be pretty good enough but i think we will get some error we will try to run and check that so first of all when we are writing this kind of a syntax using pivot we need to provide uh basically an alias for each of our sub query here so this is kind of like a sub query or i called it like a base query we need to give an alias here so i'm just going to call it like let's say uh sales data okay and then here also i need to give an alias and this one i'm just going to call it like let's say pivot table okay now i think we are pretty good now if i run okay so there's one more thing that i'm getting error operand data type where care is invalid so basically this amount field here is a where care okay we have just replaced the dollar with a null value but we still need to convert this amount into an integer so that this sum of this amount will work so that is why i'm going to use the cast function here i'm just going to say as int and i think now it should be pretty good okay so you can see that now i executed and i'm already getting the data so this is basically how pivot works in microsoft sql server now if you look at this output carefully you can see for custom one for the month of jan it's displaying 150 and for every other month it's basically displaying null if you go back here for customer one you can see that there are only these three records and that is 150 for the month of jan when it comes to customer two i think you have how many six records here and it's for the month of jan and feb you can see for jan it should be 300 and for fab it's minus 300 right and the same thing is displayed here for customer 2 for jan 300 for february minus 300 and for every other month it is null and the same thing is happening for customer 3 as well okay so now this is fine we have basically come up with the first part of our solution that is i think from our solution we have kind of done this much that is the aggregation of the sales amount for each customer for each month the next thing that we will try to do is this last record here that is the aggregation of sales amount for each month irrespective of the customer how can we do that now what we can do is if you see this data here right if i try to explain this once again this base query will just provide you the raw data based on this data sql will try to do the sum of amount okay and it will try to create multiple columns based on the values that are written from this sales date and what you have mentioned here okay and then each of these values will correspond to the values that is returned in the sales date column okay and that is how the display is happening here okay now the thing is this aggregation of the amount is happening based on each customer because this customer id here for my sales data table is basically returning me three different customers okay and hence when it's doing a sum of this amount it's doing the sum of this amount for each customer now what i need in my next step is i need to do the sum of this amount irrespective of the customers for that what i can just do is i'll just copy that entire query okay and i'm just going to use an union operator here and i will just paste the second part of this query basically the same query and i'll just do one change that is now i want to do the aggregation irrespective of the customer so instead of giving this customer id here i'll hard code a value called as total why because you can see here this last record here has customer as total so i'm just going to give this hard coded value with this what sql will do is when it's trying to do this sum of amount this customer will always be total so irrespective of what customer is it's just going to do a sum of amount for each of this month and then it's going to uh provide that output so if i run this now now you can see that this is just returning me one record why because there was just one customer that is by the name total right so the aggregation happened for this one customer only so that is why you get the complete aggregated amount but each of this amount is still getting spread across these 12 different columns that i have uh basically getting from the sales date column and that is what i have defined here okay i hope this is clear this is basically how pivot works now i am using a union because i want to basically merge this last record here and the first three records that we got from our initial query together into our final output so now if i run this now you can see i'm getting the four records this is exactly what i wanted okay so now we are pretty close there's one last thing that we need to do here is to find this that is the aggregation of sales amount for each customer irrespective of month but before i do this or in fact if i have to do this how can i do this just if you think carefully right i just need to sum up all of these 12 columns the sum of these 12 columns will basically be the output here right so you can see 150 plus all of these zeros will return me 150 and here if i do sum of all this because it's 300 and minus 300 it's going to return me zero and then again the same thing here right so i need to do a sum up all of these uh 12 columns or 12 amounts in each of these months and then that will be my total right but before i do that here you can see i have this 0 and 0 right but in my output that have got from this query it's returning me as null now i cannot sum null plus 150 will return me null okay that is not what i want so what i need to do is i need to transform this null into a 0. so whenever you need to convert a null into some other value we can use a call is function in microsoft sql server depending on other rdbms you may need to use other function but collis is a function where you need if you have to replace a null value with some other value then we can use call is now in order to transform this null into call is what i can do is i can put this entire query into a sub query or i can just use a with clause so that i can make this query a little neat because already this query is pretty big so i'm just going to say with sales data okay as and then i'm just going to move this entire query to the right and what i'll do is i'll create another with clause okay so inside this with clause i've already created this one temp table and i'm going to give it a name like sales data now i'm going to create another temp table this will be something like let's say or it's of sales data i'm just going to call it like let's say pivot data okay and what i'll do next is i'll create another temp table inside my width clause and this one i'm just going to call it like let's say final data and here i'll just say select star from that is my pivot data okay and what i'm going to do is when i'm doing this one right when i'm selecting the data from my pivot table i'm going to do this transformation such that i can transform null into a zero so first of all i'm going to fetch my customer id column that is i hope it's not too confusing because if i run this internal query here or if i just select this entire query here and if i run this you can see that i am getting this entire data now what i need to do is this column stays as it is but then for every of these other columns i need to put a college so what i'm going to do is select customer id okay and then i'll tell i'll use a callis function okay and here i will tell this one that is jan-21 okay and i'll tell if it is null then return me zero and this column i'm just going to name it like jan underscore 21 okay so this for my first column join 21 and i'll do the same for all the other 11 columns okay so i have already done that and now i think we are pretty good so what i have done is from my pivot data temp table that i have created in the above step from that data whatever i am getting this jan fab and march values i'm just using a collis function to replace null with zero and then this everything i'm putting it inside another temp table and i'm naming it like final data and finally in my main query i'm just going to query from that final data table so i'm just going to say select star from final data and now if i run this okay i'm getting customer id okay it's not customer id it is customer so now if i run this now you can see that everything is still the same the only thing is all the null values are now replaced with zero okay so i hope it is clear the query is a little big so i cannot maybe show it in a single window if i try to show it it kind of becomes too small but i hope everything is clear so from my pivot data that i generated here i got all the values but wherever there was no value it was returning null and i wanted it to be transformed into 0 because in my next step i want to sum up all of these column values so i can get the last column that is a total column okay and that is why i have used this call is function and i'm using this pivot data table this pivot data is basically the query that i wrote in the in the previous step that is inside my first with clause temp table okay now i'm calling it like a time table it's not actually a temp table but with clause kind of creates something similar to a temp table but yeah just i just refer to it like a temp table but don't treat it like a actual time table it's a temp table that with claus creates and it will last only until the execution of this query okay now this is fine the next thing that we will need to do is we will need to come up with that last column you can see here in the output the last column here is basically the total column now we need to come up with this how we can do this is we just need to sum up the values of these 12 columns or 12 months right so what i'm just going to do here is i'm just going to say select customer okay and here okay now when i'm doing this i will do that summing of all these column values but i'll also do some additional transformation that is in my final output you can see here i have all of these values but the dollar symbol is missing right so i will try to do that transformation such that i will add a dollar to the end of these amount values secondly wherever there are negative values i'll replace the negative with a parenthesis right and you can see i have a negative value here here here here and a few other places right so how i can do this is just by using a case statement i can just tell case when jan underscore 21 okay is let's say less than zero if it is less than zero then i want to concat uh that is okay so if it's less than zero i want to concat it with open parenthesis first and then i want to basically uh use that same 21 value okay and then i want to multiply it with a minus 1 because this negative symbol here should get replaced with this parenthesis right so the negative if you want to basically remove a negative value you can just multiply the value with minus one and that's what i'm doing here and after that i just need to close the parenthesis and then add the dollar symbol so i'm just going to say close the parenthesis and the dollar symbol and i think that's it this should be my concat and let's say if the value is not less than zero if the value is not less than zero then i don't need to add this parenthesis right but i still need to concat the dollar symbol so that's why i'm just going to say jan underscore 21 and then i'm just going to add a dollar symbol at the end and this is basically the end of my case statement and this whole thing i'm just going to name it like let's say jan dash 21 okay so i think that's all now if i just execute this you can see that since i only defined this one column here in my outer query just that one column is getting written and i think the value is as per how i want so there is a dollar symbol added and everything else looks fine so what i'm going to do is i'm just going to do the same copy paste of this and create it for all the other 11 months okay okay so i have done that now i have basically added the same case statement for each month and now if i run this you can see that i'm getting the data for every month and all of these negative values are now replaced with this parenthesis so everything looks fine the last thing that i will need to do here is basically add the last column that is the total column now my total column is kind of like i need to do a sum up of all of these month values right so what i just can do is i'll just tell case when so you can see here if the customer name is total if i go back to the output here if it is total then i want to display null right and only when it is other than total i want to basically do the sum aggregation here so what i'll just do is case when customer is equal to total okay then i will just display as null okay so that's it else what i can do is i can just sum all of these month values together so i'm just going to do this so you can see here i'm just adding all the month values jan feb march until december now this should be fine so what i'm just going to do is i'll just close my case and and i'm going to give a name for this like let's say total and now if i run this you can see that i'm still getting the four records and here i have got the last column that is total but the only problem here is this total value does not have a dollar symbol and this negative symbol is still shown so what i'll need to do is this case statement i used to do the transformation for each of this month values i need to do the similar transformation for this total column as well so what i can just do is here in my else part when i'm displaying these values i can put another case statement here telling that case when and i'll tell something like if this whole value is less than zero okay if this whole value is less than zero then i want to do a concat so i'm just going to say concat and here i'll just open the parentheses and the next value will be this whole value so i'm just going to copy this whole value and then the last is i need to close the parenthesis and add a dollar symbol okay so this is if the value is less than zero but if it is greater than zero then what i need to do is i need to put it in the else part i'll do a concat and here i will again display the entire value and concat with just the dollar symbol right i think that's all and i'll end this inner case here so you can see that inside the case statement i have used another case statement why because i wanted to add a dollar and transform this negative into a parenthesis okay so now if i run this entire query now you can see that i am getting my final output uh i think okay there is one problem here i am still getting minus 2 inside the parenthesis so i will need to multiply this value here with a minus 1 in order to when i am concatenating the parenthesis so that i can remove the negative value and now if i run this and you can see here the negative value is gone i think this is exactly the output that i wanted uh in basically in my problem statement okay so this basically how we have solved this problem now if i just try to uh shrink this window a little small so that you can see the entire query you can see that the query is a little long but i think i hope you understood this query and this basically how we can use pivot to solve this kind of problem now all of these scripts and sql queries will be available in my blog as well as in my discord channel so you can download it from there now let's move ahead and try to solve the same problem using oracle the syntax is almost the same there is very few changes between pivot in microsoft sql server and pivot in oracle so we'll see how this query would look like in oracle okay okay so now let's try to solve the same problem in oracle now i am connected to the oracle database and and i'm using the sql developer tool so what i'm going to do is the same query that i have written in microsoft sql server to solve this problem i'm going to paste the same query in this sql developer tool now i'm not going to write this query from scratch simply because the query is 90 same in oracle there are just few syntactical differences that i'm going to explain now okay but other than that the logic the flow the the use of pivot everything is the same okay so we'll just see what is the difference here okay so first of all the first difference is that in oracle we do not have this function called as format when we want to fetch only month and year from date instead of format we can use something called as a two cat and here i can just give the format like m vo n and i can just give y okay and then i will do the same thing in the below query here as well instead of format i'll give two care i'll replace the format yum yum yum with mvo okay so this is the first thing that you will need to do now the second thing is that inside this base query we gave an alias here and we gave an alias for our pivot part as well but when it comes to oracle oracle does not require this alias if you give an alias it's going to throw an error so second thing is we will remove this alias and we'll do this for both our union queries okay so that's the second thing now the third thing is in microsoft sql server whenever you want to basically provide a value you can provide a value within the square bracket but in oracle you cannot use square bracket but rather we can use a single quote so i'm just going to use single quotes and i can give the value like this but what will happen is the final output our column names will basically display as it is with the single quotes and i would not want that to happen so i'm just going to give an alias here like jan underscore 21 so that we can use this name wherever we have to refer to that column going ahead in our query okay so that's one thing and i'm going to do this for every single column here so i already have it with me so i'm just going to paste that value so i just replace the square brackets with the single quotes and i gave an alias here okay so that i think is the third thing and the fourth thing that we will need to do is okay so i'm just going to do the same in my below query here as well i'll replace all of these values like this and the fourth thing that we'll need to do is in oracle call is function is not supported instead of call is we can use a function called as nvl so we'll need to do that and secondly again here we are using this square bracket and the column name instead of this we can just use the column name that we basically gave above okay and i'm going to do this for every single month okay so i already have this code with me so i'm just going to paste it and that's i think is the fourth thing and then okay so let me try to run it let's see if it works and if i run it again i'm getting an error like invalid number of arguments so i think there is something else that i need to change okay the next thing that i will need to change or i think it's the fifth changes this concat function right in microsoft sql server will accept i think any number of arguments but in oracle it only accepts two arguments so if you want to concat two values then you can use the concat function but if you want to basically concatenate more than two values then instead of concat we can use the pipe operator so i can just remove this comma i can use this double pipe operator and so basically instead of concat i'm using this double pipe operator okay that's all and if you have to concatenate two values then you can still use the concat function but when you want to merge more than two values then you will need to use the concat or basically the double pipe operator okay so i'm just going to follow that here as well okay so i think that's all and i will need to do the same for every single column here so i'm just going to do that again i already have this piece of code so i'm just going to paste it here okay and okay so let me just move this a little down okay so i think that's all now okay here also i will need to replace the concat with the pipe operator so double pipe here and here as well okay and i'll remove the parentheses from the end and again in the else part as well i'll remove the concat and i'll replace the comma with the double pipe operator and i will remove the last parenthesis of concat okay so i think that's all then everything else should be the same let me run it now and now you can see that i'm already getting the output i only i think did five different changes and you can see that i am getting the output exactly how i want it so you can if you see carefully the data is exactly how it is mentioned in my output here okay so that's all this is basically how we kind of use the same query that we written in microsoft sql server and basically used it in oracle only few changes just to quickly explain the changes that i did was replaced format function with two care function and used the proper date formatting in oracle then i removed the alias that i had given for my pivot as well as for my base query okay and then i replace the square bracket with this single quote and then i give an alias for each of these columns and then i use that column name in my main query here or basically my second with clause query here and i replaced the call is function with nvl and then finally i replace the concat function with this double pipe operator to merge multiple values okay so that's all just by doing these changes the same query was able to get transformed into oracle okay i hope this is clear now let's try to solve this same problem in postgresql using the crosstab function now in order to solve the same problem in possible sql database i am using the pg admin tool and as you can see i have created the same table sales data with the same data here okay now in postgresql we do not have something called as pivot but we have something called as a crosstab function but in order to use this crosstab function we will first need to create an extension called table func okay so in order to create the extension i can just tell create extension table func okay and this will basically create an extension and and as part of this extension we can basically use the crosstab function okay now in order to use the crosstab function we basically provide the crosstab function in our from clause okay so we can just say select star from cross tab okay and then crosstab function will accept two arguments now the second argument is optional but we will see why it is important a little later first we'll just try to create with the first argument okay so the first argument the crosstab function will accept is basically your base query okay the kind of like the similar query that we created in microsoft sql server and in oracle so what i'm just going to do is i'm going back to my oracle and i will copy the base query here okay that we created so i'm just going to paste it in my postgresql so i'll just paste it here because i need to explain a little about this okay so let me minimize this okay so now this is my base query if i run this you can see that i am getting the data like how i got it in oracle and in microsoft sql server we just transformed the date to only fetch the month and the year and we transformed the amount such that we removed the dollar symbol from there okay now i can use this base query as it is inside my crosstab function but there will be some problem we will see that later for now we'll just use the same base query that we used for in other rdbms as well okay so we need to pass this sql query or kind of like our base query as a text for this crosstab function so since we are going to be passing it like a text we will need to put it inside an single quote okay so when we are enclosing basically any text inside a single quote if that text is having any other single quotes then we will need to escape it we can do that by just by putting another single quote here okay so that's what i'm just doing it here and i'll do it for everywhere where we are using this single quotes okay so that's all now i think this base query will be accepted as an argument as a text argument inside our crosstab function that's the first thing now i can also pass a second argument which basically is the list of values or kind of like the list of columns that will need to be created okay now we will use that a little later but i'll just explain you here now this base query that we are using inside our crosstab right this also has a few rules that you need to follow again like oracle and in microsoft sql server this query minimum has to return three columns now the first column has to basically be kind of like your unique row id it has to kind of represent each unique uh row that will be returned okay and then the second column is basically the list of categories that is hum all the different columns that we will need to create right as part of our pivot or as part of our crosstab those list of columns needs to be returned from this particular column here sales date okay and then finally the last column is basically the value that will get returned into each of the columns that was created from basically here that is from our second step here okay so that's all that is kind of the rule that you need to follow when you are giving an sql query here okay now once you have done this there is one additional thing that you will need to do in crosstab and that is you will basically need to provide the list of all your final columns okay once you execute this query what is the final list of column that will be returned so of course you will need to have a customer id you will need to provide that along with that all the other columns that you would want to get displayed from your final output right so you will not just need to provide the column name but also you will need to provide the data type okay so first column that i need to have in my output i know is basically my customer column and it's basically a varchar so i need to provide the data type as it is it's very important that the column values that are returned here with a particular data type that same data type you will need to provide it here along with that same column name okay so this is my first column customer id the second column would be something like let's say jan underscore 21 and this would be i think end okay and the same thing for every month feb underscore 21 and this would be int as well and so on and so forth for every other month so i'm just going to quickly uh paste it so i already have that i pasted these values and basically that's all i think this one should basically work let me run this now and you can see that now my row level data previously this base query was having data something like this right row level data 19 records and now just by putting it inside a crosstab function as an argument here okay and then passing the final list of columns you can see here whatever values that i give that will kind of be the output return from this entire query so if i run this you can see that i am getting giant 21 okay first column is customer that is this column then john 21 5 21 march etc okay if i did not provide all of these columns so let's say if i remove all of this columns and if i ran this now you see that it's only going to return the columns that you have mentioned here so this part here is very important because this is basically what your final output would look like okay based on what you have mentioned here okay now this is fine and if you look at the data carefully you are already doing this transformation from row to column but if you look carefully the data is actually not correct for example you see the very first record customer one here so if i just zoom in a little you can see that the customer one is saying jan 50 fab 50 and march 15. now if i go back to my excel and look at the input data here you can see here for the month of jan or basically for the customer one he has three amounts of basically three orders all the orders are of amount 50 but all these three are for the same month jan but in my final output here it displayed under jan feb and march now why this is happening is because in our input data for the customer one we have three records so what postgresql will do is it's just going to take one record at a time so it's going to take the first record it belongs to customer 1 right and it has a value of 50. so it's just going to post this or basically print this 50 value into the very first column it finds then it goes to the second record it finds another record for customer 1 but and again with the value 50 so it's again for the same customer but it's a next record so that next record will just get transformed into the second column here and similarly the third record will get transformed into the third column here that is into the match okay it's basically not validating if this value needs to go into jan or which particular month okay the reason why this is happening is this transformation here happens depends on the number of records that are returned from your base query okay so in this particular case when we are using crosstab in postgresql it's important that we will do the aggregation manually here okay when in microsoft sql server and in oracle we didn't have to do the aggregation that is we didn't have to do the group by we didn't have to do the sum but in crosstab we will need to do it because we will need to provide the data to postgresql in in a particular way such that for every customer for a particular month there is just one record in the base query so that all the transformation can happen accordingly okay so how we can do that is what i'm just going to do is the same base query i'll just modify it using a group by so i'm just going to add a group by here okay and i'm going to group by let's say customer id and also i'm going to group by based on each month okay so that's there and i will also do an order by here so i'll say order by one okay and everything else same here i'll just do a sum of this amount okay now this order by that i'm using here is actually very very important in crosstalk if i did not use order by the output will be completely different and we will see that in a short while okay now what i'll do is i'll just if i run this query here now you can see for each customer for a particular month there will be just one record so for customer two for jan there is one record for for feb there is another record and same way for customer three okay now we'll just try to use the same base query inside of our crosstab function so what i'm just going to do is i'll just add the group by here so i'm just going to copy this entire group by end order by okay so i'll just paste it here okay and then here i will need to use the sum aggregate function and i think that's all now if i just run this okay i'm getting an error okay because i have a single quote here i'll need to transform them into a two single quote so it kind of becomes a proper text and now if i run i'm getting another error okay now this another error here it's telling return and sql tuple descriptions are income incompatible the reason for that is i told you whatever data type is written from this query so all the column values that are returned and its data type needs to be exactly matching with the data type that you have mentioned here for each of those columns okay now if i run this query as a whole here you can see that this amount field now is returning big previously when i did not use sum it was returning me an integer but since i am using a sum it's an aggregate function this sum aggregate function will return the value which will be of data type big end okay that means all of this jan feb march all of these column values here will be in big data type and that is why i need to convert this big end basically this int into big eight okay so if i just convert all of this in into big end let me do that okay and let me just zoom in and okay so i just transformed all the end into big end and now if i run this hopefully it should work and now you can see that the transformation is working absolutely fine i'm getting the data okay now you can see for customer one for jan it's tilling 150 whereas for every other month it is telling as null okay same way for customer 2 if i go back to my excel here you can see for customer 2 i think there are this many six records so for jan he has three records and for fab i think he has three records so aggregation for jan would be 100 plus 100 plus 100 that is 300 and for feb for customer 2 there is minus 100 plus minus 100 plus minus 100 which will be minus 300 and if i look at the data here for customer 2 for jan it is plus 300 for fabric is minus 300 and then for everything else it is null okay because there is no entry basically there is no sales order for customer two for any other month right so that is fine okay so now we are getting the aggregation but if you look carefully the output that we're getting is actually not correct because for example let's look at this customer three so if i just zoom in you can see that for customer three it's saying for jan he has minus one for fab minus one and then for november and december it is null okay but if i look at my input data or kind of like my output data for jan and feb actually there is no value but for december and for november there is value under customer 3 okay because depending on what was provided so for customer 3 he had some orders in november and december but he did not have any order in jan and feb but the output that we have got from crosstab function in postgresql it kind of has done it reversely that is for jan and feb it has some value for but for november and december it does not the reason why this is happening is because as soon as crosstab will find a value it will just load that value into the very first column it finds it's not going to compare if that value should correspond to jan month feb month november month december month or etcetera okay whatever value it is finding so from this base query here whatever value it find it will just load that value into each of the columns that you have defined here okay now if you want to make sure that the values are loaded into the proper columns this is where you will need to provide a second argument okay and that second argument you can basically provide it here for your crosstab function so you can just put a comma here and then provide a cross second argument okay now the second argument is also actually a string and it kind of will accept a value so either you can pass an actually sql query which returns kind of like one column with all the 12 month values or you can basically manually specify each of the month values here okay so this is kind of like the values that you need to pass that will be returned from your sales date column okay so what i'm just going to do is i'm going to pass it here so you just need to pass it within the parentheses first value second values etc so here i'm just going to say it is going to return jan 21 and this value should be exactly same that will be returned from your sales date column okay that is your second column in your base query okay so again since it's a text i will need to escape the single character using another single character and the same thing i will need to do for every month okay so i'll just say feb 21 and i will try to do the same for every other call every other month okay so i'll just paste the values that i already have let me just zoom out a little so it becomes more clear okay so i think that's all we have and i think i'll need to remove this additional comma that i had here and i think everything else is fine so i just passed in the second argument here this argument is basically all the different values that will be written from this column the value has to be exactly a match okay okay so by adding this second argument we will then make sure that when postgresql is loading value into each of these newly created columns it's going to compare those values returned from here with the values that you have mentioned here and it's basically going to do the match and then add the values okay so i'll just run this query now before i run just check here for customer 3 you can see for jan feb he has value but for the last two column it doesn't now after i added the second argument if i run this okay so i'm getting some error let me try to fix that error first okay actually it's not value it should be values so now let me run this and now you can see it's working fine and you can see here for jan and feb now it's returning null but for december and november it's having the correct value okay so this is basically the reason why you will need to use your second argument in crosstab to make sure that the values are loaded into the correct columns okay now this is fine there is one more thing that i want to explain here is the importance of using an order by so let's say if i did not use an order by here and if i run the same query now you can see that instead of returning just three records it's returning multiple records so there are multiple records for each customer this is a weird thing that happens in crosstab in postgresql i'm going to explain you why this happens okay so what i'm going to do is i'm going to take uh screenshots i already have taken a screenshot of this entire result set here okay and i'm going to run this base query so this base query that is mentioned here right depending on what is written from this base query your crosstab is going to work okay so let's try to compare the result of this base query with the result of our crosstab so we kind of understand why order by is so important in crosstab okay so i'm just going to run this group by clause here okay and let me just zoom in a little and i'll just uh expand this okay and then let me just uh bring both the result from our crosstab as well as from our base query into a single screen okay so it becomes more easier for you to see okay so i have it here now i hope it's not too confusing the above result set that you see here basically it's a screenshot is the screenshot of my crosstab function and the below data that you see is the data from my base query now what exactly happened without order by was when i did not use order by my base query here returned the data in in any order in basically it did not follow any order so you can see that i have first two record for customer three uh then i have one record for customer two then again i have customer three records i think five records for customer three then i have a record for customer two then again one record for customer three then again a record for customer one and so on and so forth okay so this customer id is not in proper order due to this what happens is when postgresql is processing these records inside the crosstab as soon as it finds records which belong to the same customer id that is the unique identifier for this particular crosstab it's going to create one record from those combination of records so for this first two records belongs to customer 3 so when it provides the it kind of does the transformation in crosstab from these two records it's going to return one record okay so you can see here for customer three is just returning one record where you have value for august and september as you can see in the first two record okay when it comes to the third record here this belongs to customer two so for customer two is different from the customer three from the first two records so for this customer too and the very next record is again not customer two so for this sole record that is for this customer to record it's going to transform it into just one record where it's going to populate the data for february okay and that is exactly what is happening here when it comes to the next record it sees that for customer three there is one record two three four and five okay so there are five record which belongs to customer three so these five records will get basically merged together or aggregated together uh into a single record in in the crosstabs so this record number three customer three you can see here there is data populated for april may june july and then finally for november and that is exactly what you see april november july june and may okay and then finally when it comes to customer 2 record here it's going to create another record for customer 2 and it's going to populate the value for jan because here it is for jan right and the same thing happens for every other record okay i'm not sure how much you understood this if you this is not clear just go back in my video and try to watch it again because it's kind of confusing it's a weird feature that crosstab has but it's a very important feature that you need to understand just by not using an order by or not using the proper order by your entire crosstab of result might differ okay so that's the importance of using crosstab because you you need to basically your base query should provide all the data such that for the same customers all the data is in the same order okay so if it is in bundled up in different orders then it's going to create one record for each unique customer okay so that's one thing that you need to remember i think that's the major difference in crosstab i think now okay i'm going to use the order by again here to prop make the data proper order by one okay and now if i run this i'm getting the data so this basically is the first part of my output where i have got this result that is aggregation of sales order data for each customer and for each month the next thing is i need to basically i'll do one thing i will okay first of all i'm just going to remove all of this i'll copy the entire query and i will add in a union clause here to add the query again okay and let me just zoom in zoom out a little so i can because this queries are pretty big so i just want to show it as a whole so what i need to do next is i have got these three records the next thing is again similar to what we did in oracle and in microsoft sql server we need to find the aggregation of sales order for each month irrespective of the customer so for that what i'll do is instead of providing a customer id here i'll just hard code a value like total okay and then i will remove that customer id from my group by clause here and i think that's all now if i run this query it should basically return me just one record where it's basically aggregating the entire sales data irrespective of the customer but for each month as you can see here okay now since i'm using a union and if i run that entire query you can see that i'm getting the four records that i want but the order of this data is not in proper order so what i'll do is at the end i'll just say order by one okay this order by is for the entire result set okay now you can see i'm having customer one two and three in sequence and then i have the total okay so this is fine we have got basically this data here now the last two things that we will need to do is one we need to add this column that is a total column at the end and then we will need to basically add the dollar symbol uh basically suffix the dollar symbol and then replace the negative values with the parenthesis right in order to do that i will basically follow the same thing that i did in oracle and in microsoft sql server i will just move this entire query into a with clause i'm going to call it like let's say pivot data okay and i will just move this to the right okay and i will add a parenthesis here and this is basically the first time table inside my weight class i'll create another one which i'll call it like final data and this will kind of be a data where i want to transform this null values into 0 right so that then later on i can do the aggregation which i already explained when i was explaining microsoft sql server query right so in order to do that what i'm just going to do is i'll basically go back to oracle so i'll just go back to oracle i'll just copy this entire thing from this final data till the very end okay so i think till here and i'll go back to postgresql and i'll just paste that entire thing here the same thing that we did in oracle and in microsoft sql server so that's why i'm not rewriting this query there are of course few changes that we will need to do the first thing is in postgresql nvl does not work but qualis will work so i'll use callis function here instead of nvl and i'll replace it everywhere here okay and i think that's all uh probably everything else should work so let me just run this and you can see that everything worked absolutely fine so i'm still getting the final data here exactly how i wanted it in my final output okay so i have not explained the second part of this query because i already explained it in microsoft sql server the only thing that i explained here was crosstab and what is the difference if you don't use an order by and why you should be using the second argument and so on and so forth other than this the second part of the query where we needed to basically suffix each value with a dollar and then replace the negative value with parenthesis uh it's all same in all these three rdbms just uh you may need to use a different function for each rdbms okay i hope this is clear now let's go on to the last database that is mysql and try to solve the same problem using case statement okay so now finally let's move to the mysql database as you can see i'm using the mysql workbench tool and we are going to solve this problem in mysql using case statement because in mysql pivot is not supported nor crosstab is supported so whenever you have this kind of requirement to transform row level to column level data we can use case statement so let's see how we can do that now as you can see i have already created the table and the data is already loaded it's the same data and it's the same table that we have in our problem statement okay now the very first thing that we will need to do is we'll try to transform this raw data into a format that we need what are the different things that we will need to do is first i will need to remove this dollar symbol as i explained already in the previous solutions for other rdbms and then from this date field i am only interested in fetching the month and the year so we'll try to do that transformation okay so what i'm just going to do is i'm going to say customer id as customer okay and then i'm just going to say i'm going to use a function called as date format to fetch the month and the year so i'm just going to say sales date and here i'm going to give the format like let's say percentage b dash i think percentage y okay and this one i'm just going to give an alias like let's say sales date and then finally the last column is amount i'm going to replace the dollar symbol from the amount field and i'm going to replace this dollar with i think a null value and i'm going to name this column like let's say amount okay and i think that's all now if i run this now you can see i'm still having the same number of records but the data is transformed in a way that i wanted for my processing right so this is the very first thing we have built our base query the next thing is let's see what are the different things that we will need to do first we will need to basically aggregate the sales order data for each customer and then aggregate that sales amount and display it under its corresponding month right so let's try to do this so in order to do this right the concept that we can use is case statement so for example what i'm going to do is i'm going to place this base query inside a width class just so that it becomes more neater i'm going to call it like let's say base query okay and i'm going to use the as clause and here i'm just moving to the right and i need to put this query inside a parenthesis so i will do that and then finally in my main query or my outer query i'm just going to print this data just to show you that there is no difference as of now now if i just run this you can see i'm still getting okay so i'm getting an error i think what might be the error here so i am doing this so let's run this okay so i think the name is slightly different so i'm just going to use this name that is base query and now let me run this okay so i'm getting the data so in mysql i told you that we have to use case statement the reason why i need to use case statement is because the very first column that i will need in my output is this customer column i know that and i already have it here so that is fine but the all the other columns that i will need is not present in my base query or is not present in my input table right so i will kind of like need to derive those columns so whenever you need to derive those columns based on certain conditions then we can use case statement so if i have to explain you that i'm just going to give you a sample here so i'm just going to say select customer and here i'm just going to say case when okay so whenever my sales date field let's say if it is jan okay so i'm just going to copy that if it is equal to jan 21 okay then let's say i want to print something what what do i need to print so let's say i will just print the amount okay and if it is not jan i want to print it like let's say else zero okay and i'll end my case statement and i'm going to give an alias for this like let's say jan underscore 21 okay and i will do the same so for let's say for a few other months so i'm just going to copy this okay in fact i'll copy this for every other month okay so let's try to do this i'll just quickly do this okay so i have just uh done the same thing for each month so what i have done is this is my base query as you can see the data here from here i'm just fetching the customer as it is from the base query and then for each month i am creating one case statement and i am deriving a new column with the month name right so i'm then displaying the amount field if so what happens here is sql will process these records one by one okay so when it's going to process the first record it's going to see that the sales date is jan 21 and the amount is 50. so in this column it's going to print 50 okay when it's going to find this case it's going to see if the first record is having feb it's not so in this particular case only this particular record will be successful so for only for jan field this 50 will get displayed when it is going to process the second record again it's going to see that this record this case statement will be successful so it's it will going to print an amount for jan but for all the other months it's not going to it's basically going to display just zero okay because the sales date is not fair so whenever we process a customer or a record who is having so let's say feb okay in this particular case cuz two is having fab in this case this case statement will be successful here this sales date will be equal to fab 21 and it's going to print the amount that in this case is minus 100 okay and then all the other case for that particular record will basically print 0 okay so let's see how the result would look like now if i run this you can see that from row level i have already transformed it into a column level only thing is i'm getting multiple records for each customer because i have still not done the aggregation but i hope you understand this first step that is how do you create row two column so just by using a case statement and just by giving a condition in this case because each column here needs to hold value based on each month right that is why i'm using this sales data i'm matching it with a particular month and then displaying the amount if it's a match if it's not a match then i am just displaying zero okay so this basically is kind of like the skeleton to our query okay now this is fine and you can see i'm getting like what i'm getting uh one record for each data that was present in my base query but this is not how i want my final output right my final output should be such that and for now we are only implementing these three records i just need one record for each customer so this means i will need to group the data for each customer and when doing so i will need to do the sum of the amount you can see here for customer one there are three records and all three belongs to jan and the amount is 50 50 50. so when i group by customer and if i do a sum of amount i will get 150 and since the month is jam it's going to print this 150 for janna and for every other month it's going to print it like zero okay so what i will basically need to do is from this query if i just put a group by here so if i just put group by let's say customer okay and here if i just put a sum okay now when putting a sum here there are there there is one thing that you need to remember so of course i can put a sum of amount here and this should still give me the data but it's not going to give me the data how i want why first of all this is going to give me an error because since if i use the sum here and since this sales date column is not within an aggregate function and it is not part of my group by also sql is going to throw an error because as per sql whenever you are using a group by any column that you mention in the group by clause only those columns can be present in your select clause without enclosed within the aggregate function meaning that if you want to use any column here and if you don't want to put that column within the aggregate function then those columns should be part of our group by so if i put a sum of amount here for every ah column here then this sales date will not be part of my aggregate function and it is not part of my group by so it's going to throw an error one way of fixing it is just by putting the sales date inside my group by here okay but if i do that then again i'm going to not get just one record for each customer i will get multiple records because for example if you see here this customer 2 here has an entry for jan as well as for entry for fab so what will happen is when i do a group by the sales date and the customer id then it's going to create basically two groups that is one group with customer two for jan and the second group for customer two with feb okay and i do not want that to happen i want one group to be created for each unique customer right so meaning that in my group by i still only want to maintain the customer column so the other way of doing this is instead of putting this sum aggregate function for each amount here i can do this aggregation for the entire case okay so i can put the entire case within my k within my sum aggregate function in this case it's still going to aggregate the amount for each customer but when doing so it's also going to check if that record value is matching with this particular condition only if that sales amount is jan then it's going to do the aggregation of amount and for this jan column only that aggregation will get displayed okay so this way we can kind of solve this problem so i'm going to do this for every other column here so i'm just going to print this so i'll put some here and i'll need to close the parenthesis here for again for every column so i'll quickly quickly do this okay so i think that's all now if i just run this query so let's say if i run this query now you can see that from multiple records for each customer now i'm only getting three records one record for each customer okay why because i grouped the data for each customer and then i did the aggregation for the entire case now you can see that for customer one for jan it's displaying 150 whereas for all other months it's displaying zero y you can see for customer one he only has a sales order in jan when it comes to customer two he has a sales order of i think three plus three hundred in jan and minus three hundred in feb so if i go back here you can see for customer two plus three hundred in jan and minus 300 in feb and for every other month it is zero and so and the same thing happens for customer three as well okay so this way we have kind of solved the very first part of our query that is aggregating the sales order data for each customer and displaying the amount corresponding to each month in its own column okay so this is the very first thing now what i'm going to do is i have done this in my main query but i also need to do one additional thing right that is i also need to do the aggregation of the sales order uh for each month irrespective of the customer right again in order to do this i can just copy and paste this entire query and put it in with after my union clause so i can just create a union here i'll put the same query here and in the second part of this query what i'm going to do is instead of aggregating the data based on customer i i will just do the sum of the whole amount and then it will basically aggregate the amount based on the month okay so i don't need to do the aggregation for each customer so here instead of uh mentioning the customer from the base query i am going to hard code it to total why because you can see here for this last record the customer is mentioned as total so i'm just going to put it like total and i'll remove this group by because i don't need to do that okay now if i just run this entire query hopefully i should get my last record and i have got it so this is kind of like the aggregation of the sales order for each month irrespective of the customer why the customer i have already hard coded it to like some constant value that is total okay i hope this is clear so we kind of finished the two parts of this query the last part here we will need to do is find the last column that is if you see here that is this particular column total it's basically the aggregation of the sales order amount for each customer irrespective of the month okay in order to do this here you can see that i have done the aggregation for each customer but i have done it with respect to specific month right now if i do not want to consider any month then what i can do is i can just say sum of amount and this will be like my total so here it's still grouping the data based on customer but the sum of amount will happen for each customer irrespective of the month okay now since i have added this column here in my union query as well i also need to add that same column but for my total you can see here the last column that is stored for my record with this total the last column total here is displayed as null right so i'm just going to hard code it to null i'm going to tell this column name to be like total okay now if i just run this query you can see that i'm almost got the data that i want okay so the only thing that is now pending is basically to suffix a dollar symbol to each of these values and then to replace this negative symbol with the parentheses right so let's try to do that now now in order to do that i can put this entire query into a sub query but in order to make it more neat what i will do is i'll create another with clause okay and here i'm just going to call it like let's say final data okay and i'll use the as clause and i'll then move this to the right okay and i will need to put this entire query inside the parenthesis so i will do that and i will basically write a outer query here or the main query here but i'm just going to display the data from let's say this is my final data right so final data okay now if i run this i'll still get the same output because only change i did was i moved this this union query that i have written inside my second with clause or basically my same with clause but my second temp table inside the width clause and i'm naming it like final data okay and here this is where i will do the final transformation that is i'm going to print the customer and here i'm going to say basically the same thing that i did in my other rdbms so what i'll do is case when okay let's say jan underscore 21 is less than zero because if the value is negative then i want to replace this negative value with a parenthesis right as you can see here so negative values are displayed removed and they are they are basically replaced with the parenthesis and there is a dollar at the end for every value so we need to do these two things so i'm going to say if jan value is less than 0 then i will do a concat i can use the concat function here i'm going to say open parenthesis okay and then i'm just going to okay sorry i'm just going to print this jan 21 value here and i'm going to multiply this with minus 1 because i know this condition is basically when the jan underscore 21 value is less than 0. so if it is less than 0 if i multiply it with minus 1 then then the negative symbol will kind of get replaced with a positive symbol right and then i want to again concatenate this with the closed parenthesis and a dollar symbol okay and i think that's all and this is my then part in my case statement and the else part is let's say if the value is not negative then i don't need to basically put in the parenthesis but i still need to concatenate the dollar symbol so for that i'm just going to say general score 21 and then concatenate it with the dollar symbol okay and this will be my end and here i'm just going to name it like general score 21 okay and i will need to do this same okay so before i do it for the every month let me just execute this let's see if i'm getting the output and you can see i'm getting the output for one month now i will do the same for every other month okay so i already have the snippet of this code so i'm just going to paste it here okay so now i have basically done the same thing for every other month as you can see here and here i was basically printing i need to print the value or basically my column name would should be like jan-21 as you can see here that is expected column name right and since it's a dash i cannot basically if i just give this without the double quotes it's not going to accept so i need to give it within the double quotes okay so if i run this i think this should probably work and you can see i'm getting all the data the only thing missing here is the last column that is total so we'll try to add that last column as well so i'm just going to copy this uh yes okay and i will replace this december with total okay now for this total i need to do one small additional change okay and that is so let's say if i run this you can see that i am getting the data as i want but here the last column where it should have been null it's displaying with the dollar symbol so if because i'm getting this null is from this second part of my union query here where i was aggregating the data for each month irrespective of the customer right that is where we had hardcoded this to null so i need to handle that null value so what i'm going to do is i'm going to put this entire case statement inside another case statement i'm going to say case when total is equal to null then i want to still display as null okay only for other cases that is in my else part i will do this entire case statement okay where i am basically concatenating with the dollar symbol or replacing the negative with the parentheses etcetera okay and then once this case statement is done i will again end my outer case statement as like this okay so i think this should probably work now let me run this and you can see that i it has worked and i'm getting the final output like how i it okay so all of this code the query the data set everything is available in my data in in my blog and in my discord channel so if you want you can download it and you can practice it on your own okay so i hope this video was helpful thank you so much for watching and see you soon in the next video bye
Info
Channel: techTFQ
Views: 103,250
Rating: undefined out of 5
Keywords: pivot, pivot in sql, crosstab in sql, sql query, solving sql query, solving sql queries, sql interview queries, sql interview query, solving sql interview query, rows to column using pivot, PIVOT in Oracle, CROSSTAB in PostgreSQL, PIVOT in MySQL, PIVOT in PostgreSQL, PIVOT in SQL Server, PIVOT in Microsoft SQL Server, practice sql queries, sql pivot, learn sql, sql tutorial, sql interview questions, complex sql queries, practice complex sql queries, sql queries tutorial
Id: 4p-G7fGhqRk
Channel Id: undefined
Length: 73min 11sec (4391 seconds)
Published: Wed May 18 2022
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.