With SQL Server it is possible to Rotate columns in lines, to do this, it makes use the key word UNPIVOT. The syntax is special because it is not intuitive, this sample query transforms columns into rows of a SQL Server table. It is indeed useful to transpose the columns into rows for easier management, such as generic management of a field, and not management for each column.
Transforming columns of a table into rows with a UNPIVOT query
Creating the sample table to transpose with SQL Server
We start by creating the table used for our example. It has a column that contains the type of sales and a column for each month of the year.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[dbo].[SALES_TYPES]') AND type in (N'U') ) BEGIN DROP TABLE [dbo].[SALES_TYPES] END; -- Creation de la table d'exemple VENTES CREATE TABLE [dbo].[VENTES_TYPES] ( [TYPE_VENTE] nvarchar (20), [January] numeric (5), [February] numeric (5), [March] numeric (5), [April] numeric (5), [May] numeric (5), [June] numeric (5), [July] numeric (5), [August] numeric (5), [September] numeric (5), [October] numeric (5), [November] numeric (5), [December] numeric (5) ); |
Inserting a few lines to transpose in the table
We then insert the data into our table to rotate or transpose.
1 2 3 4 5 6 7 | INSERT INTO [SALES_TYPES] VALUES ('Direct Sales', 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 11000, 12000); INSERT INTO [SALES_TYPES] VALUES ('Discount', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120); INSERT INTO [SALES_TYPES] VALUES ('Assets', 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60); INSERT INTO [SALES_TYPES] VALUES ('Returns', 25, 50, 75, 100, 125, 150, 175, 200, 225, 250, 275, 300); SELECT * FROM [dbo]. [SALES_TYPES]; |
Writing the SQL query with the keyword UNPIVOT
We can now write the query UNPIVOT, in this example we pass the columns in lines. That is to say that for each type of sale and each month in columns, one obtains a created line.
Either 4 types of sales * 12 months = 48 lines transposed.
1 2 3 4 5 6 7 8 9 10 11 | SELECT Sales_Type, Month, Amount FROM ( SELECT Type_Sale, January, February, March, April, May, June, July, August, September, October, November, December FROM [dbo]. [SALES_TYPES] ) VT A PIVOT (Amount FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December) ) AS VT_Transposed; |