How to get sql pivot multiple columns?
What is the best way to 'flatten' tables into a single row?
For example, with the following table:
+-----+-------+-------------+------------------+
| Id | hProp | iDayOfMonth | dblTargetPercent |
+-----+-------+-------------+------------------+
| 117 | 10 | 5 | 0.1400 |
| 118 | 10 | 10 | 0.0500 |
| 119 | 10 | 15 | 0.0100 |
| 120 | 10 | 20 | 0.0100 |
+-----+-------+-------------+------------------+
I would like to produce the following table:
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
| hProp | iDateTarget1 | dblPercentTarget1 | iDateTarget2 | dblPercentTarget2 | iDateTarget3 | dblPercentTarget3 | iDateTarget4 | dblPercentTarget4 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
| 10 | 5 | 0.14 | 10 | 0.05 | 15 | 0.01 | 20 | 0.01 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
I have managed to do this using a pivot and then rejoining the original table several times, but I'm fairly sure there is a better way. This works as expected:
select
X0.hProp,
X0.iDateTarget1,
X1.dblTargetPercent [dblPercentTarget1],
X0.iDateTarget2,
X2.dblTargetPercent [dblPercentTarget2],
X0.iDateTarget3,
X3.dblTargetPercent [dblPercentTarget3],
X0.iDateTarget4,
X4.dblTargetPercent [dblPercentTarget4]
from (
select
hProp,
max([1]) [iDateTarget1],
max([2]) [iDateTarget2],
max([3]) [iDateTarget3],
max([4]) [iDateTarget4]
from (
select
*,
rank() over (partition by hProp order by iWeek) rank#
from [Table X]
) T
pivot (max(iWeek) for rank# in ([1],[2],[3], [4])) pv
group by hProp
) X0
left join [Table X] X1 on X1.hprop = X0.hProp and X1.iWeek = X0.iDateTarget1
left join [Table X] X2 on X2.hprop = X0.hProp and X2.iWeek = X0.iDateTarget2
left join [Table X] X3 on X3.hprop = X0.hProp and X3.iWeek = X0.iDateTarget3
left join [Table X] X4 on X4.hprop = X0.hProp and X4.iWeek = X0.iDateTarget4
Here is one way of getting the result set you want without doing the multiple joins. It takes a little more setup and uses two pivot operations instead of one, but avoids the SQL pivot multiple columns joins.
I admit that I had to look it up, but Ken O'Bonn had a great article. https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/
/** Build up a Table to work with. **/
DECLARE @T TABLE
(
ID INT NOT NULL PRIMARY KEY
, hProp INT NOT NULL
, iDayOfMonth INT NOT NULL
, dblTargetPercent DECIMAL(6,4) NOT NULL
)
INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
, (118, 10, 10, 0.0500)
, (119, 10, 15, 0.0100)
, (120, 10, 20, 0.0100)
/** Create a CTE and give us predictable names to work with for
date and percentage
**/
;WITH CTE_Rank AS
(
SELECT ID
, hProp
, iDayOfMonth
, dblTargetPercent
, sDateName = 'iDateTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
, sPercentName = 'dblPercentTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
FROM @T
)
SELECT hProp
, iDateTarget1 = MAX(iDateTarget1)
, dblPercentTarget1 = MAX(dblPercentTarget1)
, iDateTarget2 = MAX(iDateTarget2)
, dblPercentTarget2 = MAX(dblPercentTarget2)
, iDateTarget3 = MAX(iDateTarget3)
, dblPercentTarget3 = MAX(dblPercentTarget3)
, iDateTarget4 = MAX(iDateTarget4)
, dblPercentTarget4 = MAX(dblPercentTarget4)
FROM CTE_Rank AS R
PIVOT(MAX(iDayOfMonth) FOR sDateName IN ([iDateTarget1], [iDateTarget2], [iDateTarget3], [iDateTarget4])) AS DayOfMonthName
PIVOT(MAX(dblTargetPercent) FOR sPercentName IN (dblPercentTarget1, dblPercentTarget2, dblPercentTarget3, dblPercentTarget4)) AS TargetPercentName
GROUP BY hProp
Answer (1)
To pivot multiple columns in SQL, you can use the PIVOT function along with aggregation functions like MAX, MIN, SUM, etc., to transform row values into columns. Here's a general syntax for pivoting multiple columns:
SELECT pivot_column,
[pivot_value1], [pivot_value2], ..., [pivot_valueN]
FROM (
SELECT group_column, pivot_column, value_column1, value_column2, ..., value_columnN
FROM your_table
) AS SourceTable
PIVOT (
aggregation_function(value_column)
FOR pivot_column IN ([pivot_value1], [pivot_value2], ..., [pivot_valueN])
) AS PivotTable;Let's break down the syntax:
- pivot_column: This is the column whose distinct values will become the new columns.
- [pivot_value1], [pivot_value2], ..., [pivot_valueN]: These are the values from the pivot_column that you want to pivot into columns.
- group_column: This column will remain in the result set to group the data.
- value_column1, value_column2, ..., value_columnN: These are the columns whose values you want to pivot.
- aggregation_function: It's applied to aggregate the values in value_column for each combination of group_column and pivot_column.
Here's a simple example to illustrate:
Let's say you have a table Sales with columns Year, Month, Product, and Revenue. You want to pivot the Product and Revenue columns based on the Year and Month.
SELECT Year, Month, [Product1_Revenue] AS Product1, [Product2_Revenue] AS Product2, [Product3_Revenue] AS Product3FROM ( SELECT Year, Month, Product, Revenue FROM Sales) AS SourceTablePIVOT ( SUM(Revenue) FOR Product IN ([Product1_Revenue], [Product2_Revenue], [Product3_Revenue])) AS PivotTable;This query will pivot the Product and Revenue columns into separate columns for each Year and Month, summing up the revenues for each product.
Adjust the column names and aggregation functions (SUM, MAX, MIN, etc.) according to your specific requirements.