How to Have Multiple Pivots Using the Same Pivot Column Using SQL Server

Is it possible to have multiple pivots using the same pivot column using SQL Server

My suggestion would be to apply both the UNPIVOT and the PIVOT functions to get the result.

The UNPIVOT will turn the PurchasingValue and SellingValue columns into rows. Once this is done, then you can pivot the data into your result.

The code will be:

select *
from
(
  select itemid, 
    case 
      when col = 'PurchasingValue' then 'P'
      when col = 'SellingValue' then 'S'
    end + cast(year as varchar(4)) new_col,
    value
  from yourtable
  unpivot
  (
    value
    for col in ([PurchasingValue], [SellingValue])
  ) unpiv
) src
pivot
(
  max(value)
  for new_col in (P2000, P2001, P2002, P2003,
                  S2000, S2001, S2002, S2003)
) piv;

See SQL Fiddle with Demo. The result is:

| ITEMID | P2000 | P2001 | P2002 | P2003 | S2000 | S2001 | S2002 | S2003 |
--------------------------------------------------------------------------
|      1 |  1000 |  1100 |  1200 |  1300 |   900 |   990 |  1080 |  1170 |
|      2 |   500 |   550 |   600 |   650 |   450 |   495 |   540 |   585 |

In SQL Server 2008+ you can use CROSS APPLY with VALUES along with the PIVOT function:

select *
from
(
  select itemid,
    col+cast(year as varchar(4)) new_col,
    value
  from yourtable
  cross apply
  (
    VALUES
        (PurchasingValue, 'P'),
        (SellingValue, 'S')
   ) x (value, col)
) src
pivot
(
  max(value)
  for new_col in (P2000, P2001, P2002, P2003,
                  S2000, S2001, S2002, S2003)
) piv

See SQL Fiddle with Demo

SQLServer - Multiple PIVOT on same columns

To pivot multiple column you need to useMuliple Pivot's not multiple Aggregates. Try this.

SELECT ID,
       NAME,
       Max([Raised to Supplier(PLANED)])[Raised to Supplier(PLANED)],
       Max([Base Test Date(PLANED)])[Base Test Date(PLANED)],
       Max([Washing Approval(PLANED)])[Washing Approval(PLANED)],
       Max([Raised to Supplier(ACTUAL)])[Raised to Supplier(ACTUAL)],
       Max([Base Test Date(ACTUAL)])[Base Test Date(ACTUAL)],
       Max([Washing Approval(ACTUAL)])[Washing Approval(ACTUAL)]
FROM   (SELECT 1 ID,'45rpm' NAME,'Raised to Supplier' + '(PLANED)' MSNAME_pl,'Raised to Supplier' + '(ACTUAL)' MSNAME_ac,'2014-12-17' PLANED,'2015-12-17' ACTUAL
        UNION ALL
        SELECT 1,'45rpm','Base Test Date' + '(PLANED)','Base Test Date' + '(ACTUAL)','2014-12-18','2015-12-18'
        UNION ALL
        SELECT 1,'45rpm','Washing Approval' + '(PLANED)','Washing Approval' + '(ACTUAL)','2014-12-19','2015-12-19') a
       PIVOT ( Max(PLANED)
             FOR MSNAME_pl IN ([Raised to Supplier(PLANED)],
                               [Base Test Date(PLANED)],
                               [Washing Approval(PLANED)]) ) AS p1 

        PIVOT ( MAX(ACTUAL) 
              FOR MSNAME_ac IN ([Raised to Supplier(ACTUAL)],
                                [Base Test Date(ACTUAL)],
                                [Washing Approval(ACTUAL)])) p2
GROUP BY ID, NAME

SQL Pivot table, with multiple pivots on criteria

Use conditional expressions to emulate data pivot. Example:

SELECT fyear, Month, Monthname, Count(*) AS CountALL, Sum(total_number_requested) AS TotNum, 
Sum(IIf(reservation_status = "Order Created", total_number_Requested, Null)) AS SumCreated
FROM tablename
GROUP BY fyear, Month, MonthName

More info:

SQLServer - Multiple PIVOT on same columns

Crosstab Query on multiple data points

SQL Query for multiple Pivot on same column

Use conditional aggregation:

SELECT MAX(CASE Question WHEN 'Email' THEN Response END) AS Email,
       MAX(CASE Question WHEN 'Reason' THEN Response END) AS Reason,
       MAX(CASE Question WHEN 'Action' THEN Response END) AS Action,
       MAX(CASE Question WHEN 'Reason' THEN TranslatedResponse END) AS TranslatedReason,
       MAX(CASE Question WHEN 'Action' THEN TranslatedResponse END) AS TranslatedAction,
       activityid,
       createdon
FROM dbo.YourTable
GROUP BY activityid,
         createdon;

How to PIVOT multiple columns using SQL Server

You just need to pivot twice and combine the results, e.g.:

-- Setup example data...
drop table if exists #Example;
create table #Example (
  VendorId int,
  Category varchar(10),
  FirstSaleDate date,
  StoreId int
);

insert #Example (VendorId, [Category], FirstSaleDate, StoreId)
values
  (1, 'Car', '2021-01-01', 12),
  (1, 'Clothes', '2021-01-02', 13),
  (1, 'Toys', '2021-01-03', 14),
  (1, 'Food', '2021-01-04', 15),
  (1, 'Others', '2021-01-05', 15);

-- Pivot data...
with FirstSales as (
  select VendorId, Category, FirstSaleDate from #Example
), Stores as (
  select VendorId, 'StoreId_' + Category as Category, StoreId from #Example
)
select
  FirstSales.VendorId,
  Car, StoreId_Car,
  Clothes, StoreId_Clothes,
  Toys, StoreId_Toys,
  Food, StoreId_Food,
  Others, StoreId_Others
from (
  select VendorId, Car, Clothes, Toys, Food, Others
  from FirstSales
  pivot (min(FirstSaleDate) for Category in ([Car], [Clothes], [Toys], [Food], [Others])) as pvt
) as FirstSales
join (
  select VendorId, StoreId_Car, StoreId_Clothes, StoreId_Toys, StoreId_Food, StoreId_Others
  from Stores
  pivot (min(StoreId) for Category in ([StoreId_Car], [StoreId_Clothes], [StoreId_Toys], [StoreId_Food], [StoreId_Others])) as pvt
) as Stores on Stores.VendorId=FirstSales.VendorId;

SQL Server Pivot multiple values with many rows

This is what I eventually came up with, piecing things together. It will dynamically figure out X number of columns to pivot (@cols) and then include them in the pivot result...

DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.dpt) 
                      from etl_insight_counts
                      cross apply 
                      (
                        select department as dpt 
                        union all
                        select department as dpt2
                      ) c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = N'select *
from
(
  select id_request, department, employees, max(ca.up_down) as ud
  from etl_insight_counts 
  Group By id_request, department, employees
) d
pivot
(
  max(employees)
  for department in ('+@cols+')
) piv'

exec sp_executesql @query;

For each value (Example: max(ca.up_down) ) you need a separate pivot query. I put each pivot query into a View. For simplicity I use MS-Access to filter the result by any of the known columns in the result and optionally connect the view columns to other data sources to support quick data analysis. Hope this helps someone in the future.

Multiple Pivot with an existing sql query working contains pivot

From what I understand from the question is can we have multiple pivots and want to integrate using both the queries shared. Yes, we can go with the approach of CTE until same column is present in both the sub-queries. Below is the query that might be helpful.

; with pivot_1 as
(
select pvt1.PlanId as pvt1PlanID,pvt1.[1] Jan,pvt1.[2]Feb,pvt1.[3]Mar,pvt1.[4]Apr,pvt1.[5]May,pvt1.[6]Jun,pvt1.[7] July,pvt1.[8]Aug,pvt1.[9]Sep,pvt1.[10]Oct,pvt1.[11]Nov,pvt1.[12] Dec
from(
select Plan.Id PlanId,PlanMonth.Amount,PlanMonth.Month 
from PlanTable
left join PlanMonth on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (Amount)
FOR Month IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt1
), pivot_2 as (
select pvt2.PlanId as pvt2PlanID,pvt2.[1] LockAmount1,pvt2.[2] LockAmount2,pvt2.[3] LockAmount3,pvt2.[4] LockAmount4 
from(
select Plan.Id PlanId,LockAmountTable.LockAmount,LockAmountTable.NumForecast 
from PlanTable

left join LockAmountTable on
LockAmount.IdPlan = rpo.Id
)p
PIVOT
(
SUM (LockAmount)
FOR NumForecast IN
( [1][1],[2],[3],[4])
) AS pvt2)
select * from pivot_1 p1 
inner join pivot_2 p2 on p1.pvt1PlanID=p2.pvt2PlanID

TSQL PIVOT MULTIPLE COLUMNS

Since you want to pivot multiple columns of data, I would first suggest unpivoting the result, score and grade columns so you don't have multiple columns but you will have multiple rows.

Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will be similar to:

select ratio, col, value
from GRAND_TOTALS
cross apply
(
  select 'result', cast(result as varchar(10)) union all
  select 'score', cast(score as varchar(10)) union all
  select 'grade', grade
) c(col, value)

See SQL Fiddle with Demo. Once the data has been unpivoted, then you can apply the PIVOT function:

select ratio = col,
  [current ratio], [gearing ratio], [performance ratio], total
from
(
  select ratio, col, value
  from GRAND_TOTALS
  cross apply
  (
    select 'result', cast(result as varchar(10)) union all
    select 'score', cast(score as varchar(10)) union all
    select 'grade', grade
  ) c(col, value)
) d
pivot
(
  max(value)
  for ratio in ([current ratio], [gearing ratio], [performance ratio], total)
) piv;

See SQL Fiddle with Demo. This will give you the result:

|  RATIO | CURRENT RATIO | GEARING RATIO | PERFORMANCE RATIO |     TOTAL |
|--------|---------------|---------------|-------------------|-----------|
|  grade |          Good |          Good |      Satisfactory |      Good |
| result |       1.29400 |       0.33840 |           0.04270 |    (null) |
|  score |      60.00000 |      70.00000 |          50.00000 | 180.00000 |

---

Related Topics