Turning columns into rows
In the previous exercise, you turned the names of the products you had in the rows into columns, and then you summarized the units of the products for every year.
Suppose you stored the result from the previous exercise in a new table called pivot_sales, and now you want to turn the columns notebooks, pencils, and crayons into row values.
The expected result will be:
| year_of_sale | units | product_name |
|--------------|-------|--------------|
| 2018 | 150 | notebooks |
| 2018 | 150 | pencils |
| 2018 | 80 | crayons |
| 2019 | 230 | notebooks |
| 2019 | 130 | pencils |
| 2019 | 170 | crayons |
This is a part of the course
“Cleaning Data in SQL Server Databases”
Exercise instructions
- Use the appropriate operator to convert columns into rows.
- Write the name of the resulting column that will contain the turned columns.
- Write the names of the columns you want to turn into rows.
- Give to the
UNPIVOToperator the aliasunpivot_sales.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT * FROM pivot_sales
-- Use the operator to convert columns into rows
___
-- The resulting column that will contain the turned columns into rows
(units FOR ___ IN (___, ___, ___))
-- Give the alias name
AS ___This exercise is part of the course
Cleaning Data in SQL Server Databases
Develop the skills you need to clean raw data and transform it into accurate insights.
In this final chapter, you will learn how to combine and aggregate data of some columns into one, split data of one column into more columns, and transform rows into columns and vice versa.
Exercise 1: Combining data of some columns into one columnExercise 2: Combining cities and states using +Exercise 3: Concatenating cities and statesExercise 4: Working with DATEFROMPARTS()Exercise 5: Splitting data of one column into more columnsExercise 6: Using SUBSTRING() and CHARINDEX()Exercise 7: Using RIGHT() , LEFT() and REVERSE()Exercise 8: SUBSTRING() or CHARINDEX()?Exercise 9: Transforming rows into columns and vice versaExercise 10: PIVOT or UNPIVOT?Exercise 11: Turning rows into columnsExercise 12: Turning columns into rowsExercise 13: Congratulations!What is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.