{"id":30320,"date":"2024-02-27T05:48:00","date_gmt":"2024-02-27T04:48:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=30320"},"modified":"2024-02-29T19:18:01","modified_gmt":"2024-02-29T18:18:01","slug":"sql-server-pivot-with-multiple-fixed-columns","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-pivot-with-multiple-fixed-columns\/","title":{"rendered":"How to Use SQL Server PIVOT with Multiple Fixed Columns ?"},"content":{"rendered":"\n

In a previous technical article, we learned the PIVOT query with one unique fixed column, this time we’ll use a more complex use case, a SQL Server PIVOT case with multiple fixed columns. Indeed, in this different example, the table to pivot has 2 fixed non numeric columns. So the two columns will not be aggregated or calculated and they are also not pivoted.<\/p>\n\n\n\n

1. Create a SQL table with two or more fixed columns to Pivot<\/h2>\n\n\n\n

Here we use the same process as in the previous article on the PIVOT operator.<\/strong><\/a> We simply group the data in a different way. Open SSMS<\/strong><\/a> and execute this query to cerate the table and insert some sample data. Indeed, we simply added the customer ID and Name as 2 new columns.<\/p>\n\n\n\n

-- Create the sales table with 2 additional columns\n-- The customer ID and the customer name\nCREATE table [dbo].[SALES_with_Customers] (\n    [MONTH]         NVARCHAR(20),\n    [CUSTOMER_ID]   NVARCHAR(20),\n    [CUSTOMER_NAME] NVARCHAR(20),\n    [AMOUNT]        NUMERIC(5)\n)\nGO\n\n-- Insert first sales amount for each month for Customer-001\nINSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1000)\nINSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 3000)\nINSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 4000)\nINSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 5000)\nINSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 6000)\n-- second step for the first customer\nINSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1500)\nINSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 1500)\nINSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 1500)\nINSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 1500)\nINSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 1500)\nINSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 1500)\n\n-- Insert first sales amounts for each month for the Customer-002\nINSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 1100)\nINSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2200)\nINSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 3300)\nINSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 4400)\nINSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 5500)\nINSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 6600)\n-- Insert second sales amount for each month for Customer-002\nINSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 2000)\nINSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 2000)\n\n-- Check inserted data\nSELECT \t*\nFROM \tdbo.[SALES_with_Customers];\n<\/pre>\n\n\n\n
Note that the source table contains 12 lines per Customer ID and Customer Name, for the 6 first months of the year.<\/p>\n\n\n
\n
\"Use
Use a query in SSMS to create the table with multiple fixed columns to pivot <\/em><\/figcaption><\/figure><\/div>\n\n\n
2. Use the PIVOT operator to manage multiple fixed fields<\/h2>\n\n\n\n
Once the new table is created, build the pivot query. The exact method is used here. The only difference with the more simple PIVOT example<\/strong><\/a> is that all the named columns are listed in the first select statement.<\/p>\n\n\n\n
SELECT \t*\nFROM (\n    SELECT\n\t  [MONTH],\n\t  [CUSTOMER_ID],\n\t  [CUSTOMER_NAME],\n\t  [AMOUNT]\n    FROM dbo.SALES_with_Customers\n) AS Source_Table\n\nPIVOT (\n    AVG(AMOUNT)\n    FOR MONTH IN ([January], [February], [March], [April], [May], [June])\n) AS Pivot_Table;\n<\/pre>\n\n\n\n
Build the query using the same three steps.<\/p>\n\n\n\n
    \n
  1. From the source table, select all the columns to pivot and to display: Month, Customer_Id, Customer_Name and Amount<\/em><\/strong><\/li>\n\n\n\n
  2. Configure the pivot column and the aggregation, here it’s AVG(AMOUNT)<\/em><\/strong><\/li>\n\n\n\n
  3. Use the PIVOT operator<\/em><\/strong> to execute the transformation on the selected data.<\/li>\n<\/ol>\n\n\n
    \n
    \"SQL
    SQL Server PIVOT query example explained <\/em><\/figcaption><\/figure><\/div>\n\n\n
    Finally execute the query and check the calculated average of the sales column, displayed in the Amount<\/em> column.<\/p>\n\n\n\n
      \n
    1. The first customer’s data<\/em> is available in one unique line now.<\/li>\n\n\n\n
    2. Same for Customer-002<\/em>.<\/li>\n\n\n\n
    3. Each month is now displayed in a specific dedicated column<\/em>. <\/li>\n<\/ol>\n\n\n
      \n
      \"Table
      Table before and after the PIVOT of the rows into columns with SQL Server<\/em><\/figcaption><\/figure><\/div>\n\n\n
      The ultimate goal of aggregation can be very different in the usage, it can be to use data for Reporting using one of the tools on the market<\/a>. But it can also be to limit the number of lines stored and have a better management of databases volumetry.<\/p>\n\n\n\n
      About the T-SQL PIVOT operator with fixed data<\/h3>\n\n\n\n
      In conclusion, this exploration of SQL Server PIVOT with multiple fixed columns significantly enhances our ability to manage and interpret complex datasets. By maintaining certain columns fixed while pivoting others, we unlock new dimensions of data analysis and reporting, showcasing the versatility and power of SQL Server for sophisticated data manipulation tasks.<\/p>\n\n\n\n
      Tutorials on Pivot and Unpivot data operations using MS technologies<\/h5>\n\n\n\n