So, there is another technique to obtaine same unpivot operation by using cross apply + value. Can you make a video that describes this approach in details, thank you in advance.
I will make a video on this but in short you need to replace NULLs with a value, make sure it’s something that doesn’t appear in the data then perform the UNPIVOT then replace vales with NULLs using NULLIF.
1. In your PIVOT video ruclips.net/video/ozy31aJpW-o/видео.html&ab_channel=BeardedDev you mentioned an aggregation function. Why doesn't UNPIVOT also need/allow an aggregate? 2. Is it something to do with the aggregation after pivot is an irreversible operation and data --> PIVOT (with aggregate used) --> UNPIVOT --> does not recover data? Practically why would people want to unpivot if it does not do aggregation and thus does not generate new information? (any reasons beyond just presentation?) 3. How can we show the null cells (in the pivot table) after UNPIVOT? 4. Is there a link to the video about why all TINYINT when unpivot?
1. UNPIVOT is a process of moving data from columns to rows, the aim is really to get the data in to a useable tabular format therefore an aggregation is not applicable, once the data is in the required format then aggregation can take place e.g. imagine if I have columns customerid and then one column for each month of the year that stores sales figures, if I want to get a total for each month of the year then I can perform an aggregation on each column, if I want to get the total I need to use an expression January + February + March etc, however if I move the data from columns to rows and I have columns customerid, month and sales it is now much easier to work with and perform aggregations 2. A lot of people mistake UNPIVOT as the reverse of PIVOT, the naming doesn't really help, think about when performing an aggregation e.g. if we have a table that stores customerid and sales, we then perform a SUM on sales, now all we have is a sales total, if we don't have the original data how can we reverse this process, we don't know the sales amount by customer, all we have is a total. As mentioned above we use UNPIVOT typically where our data is in a format that makes the data difficult to work with, it's more than just presentation, a good example is when we are supplied with spreadsheets that are in a matrix/pivot format, these are great for visualisations but not so good in database, we want to store data efficiently and perform calculations easily 3. To show NULLs is a two step process, as the UNPIVOT operation will remove them you need to add a placeholder, e.g. ISNULL(Value, 99) before UNPIVOT - make sure this value doesn't mean anything in your data otherwise it can be confusing, then change the placeholder value to NULL e.g. NULLIF(Value, 99) 4. TINYINT is a data type I used in the video, simply because the values stored could only be in the range 0 - 5, you will need to use the correct data type for your data
@@BeardedDevData Thanks for the explanation, now I can see how difficult it is to sum across a row, where we have to write col1+col2+..., instead of SUM(col) down a column. Nice hack to make NULL remain.
Still helping people 2 years later. I appreciate this so much!! Thank you!
Glad I could help!
Very useful and clear. Helped me solve my challenge. Thank you!
Clear and well explained! Thanks for the turoial!!
No problem 👍
Thanks beardo!
Thank you!
Thanks for the turoial
So, there is another technique to obtaine same unpivot operation by using cross apply + value. Can you make a video that describes this approach in details, thank you in advance.
That's a great idea, I'm taking the holidays off but will be uploading regularly early next year.
How to deal with NULL values in unpivot? I have some null values in value column? thx.
I will make a video on this but in short you need to replace NULLs with a value, make sure it’s something that doesn’t appear in the data then perform the UNPIVOT then replace vales with NULLs using NULLIF.
1. In your PIVOT video ruclips.net/video/ozy31aJpW-o/видео.html&ab_channel=BeardedDev you mentioned an aggregation function. Why doesn't UNPIVOT also need/allow an aggregate?
2. Is it something to do with the aggregation after pivot is an irreversible operation and data --> PIVOT (with aggregate used) --> UNPIVOT --> does not recover data?
Practically why would people want to unpivot if it does not do aggregation and thus does not generate new information? (any reasons beyond just presentation?)
3. How can we show the null cells (in the pivot table) after UNPIVOT?
4. Is there a link to the video about why all TINYINT when unpivot?
1. UNPIVOT is a process of moving data from columns to rows, the aim is really to get the data in to a useable tabular format therefore an aggregation is not applicable, once the data is in the required format then aggregation can take place e.g. imagine if I have columns customerid and then one column for each month of the year that stores sales figures, if I want to get a total for each month of the year then I can perform an aggregation on each column, if I want to get the total I need to use an expression January + February + March etc, however if I move the data from columns to rows and I have columns customerid, month and sales it is now much easier to work with and perform aggregations
2. A lot of people mistake UNPIVOT as the reverse of PIVOT, the naming doesn't really help, think about when performing an aggregation e.g. if we have a table that stores customerid and sales, we then perform a SUM on sales, now all we have is a sales total, if we don't have the original data how can we reverse this process, we don't know the sales amount by customer, all we have is a total. As mentioned above we use UNPIVOT typically where our data is in a format that makes the data difficult to work with, it's more than just presentation, a good example is when we are supplied with spreadsheets that are in a matrix/pivot format, these are great for visualisations but not so good in database, we want to store data efficiently and perform calculations easily
3. To show NULLs is a two step process, as the UNPIVOT operation will remove them you need to add a placeholder, e.g. ISNULL(Value, 99) before UNPIVOT - make sure this value doesn't mean anything in your data otherwise it can be confusing, then change the placeholder value to NULL e.g. NULLIF(Value, 99)
4. TINYINT is a data type I used in the video, simply because the values stored could only be in the range 0 - 5, you will need to use the correct data type for your data
@@BeardedDevData Thanks for the explanation, now I can see how difficult it is to sum across a row, where we have to write col1+col2+..., instead of SUM(col) down a column. Nice hack to make NULL remain.