CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
DevTN
DevTN

Reputation: 593

How to convert table rows data to a pivot table in SQL Server

I have a table as below 

DECLARE @Territory_Month TABLE 
                         (
                             Territory VARCHAR(20), 
                             [Order Status] VARCHAR(20), 
                             [count Order Number] INT,
                             [sum Dollar Revenue] FLOAT
                         );

INSERT INTO @Territory_Month (Territory, [Order Status], [count Order Number], [sum Dollar Revenue])
VALUES ('BENELUX', 'RELEASED', 108, 20363879.11),
       ('CENTRAL EUROPE', 'RELEASED', 615, 11502633.14),
       ('EAST EUROPE', 'RELEASED', 292, 4689655.19),
       ('FRANCE', 'RELEASED', 228, 5333825.69),
       ('IBERIA', 'RELEASED', 82, 1985003.46),
       ('ITALY & ISRAEL', 'RELEASED', 211, 3844900.74),
       ('META', 'RELEASED', 173, 3478765.18),
       ('NORDICS', 'RELEASED', 132, 4017755.1252),
       ('SEE', 'RELEASED', 326, 2211346.28),
       ('UKI', 'RELEASED', 124, 17541413.6),
       ('BENELUX', 'UNRELEASED', 33, 505935.16),
       ('CENTRAL EUROPE', 'UNRELEASED', 111, 597457.364505),
       ('EAST EUROPE', 'UNRELEASED', 96, 2411721.25),
       ('FRANCE', 'UNRELEASED', 41, 466737.3675),
       ('IBERIA', 'UNRELEASED', 20, 112696.37),
       ('ITALY & ISRAEL', 'UNRELEASED', 129, 928122.01),
       ('META', 'UNRELEASED', 156, 771615.44),
       ('NORDICS', 'UNRELEASED', 34, 778853.82),
       ('SEE', 'UNRELEASED', 81, 260567.31),
       ('UKI', 'UNRELEASED', 115, 6335067.356471);

I want to create a pivot table based on this row data. The result should look as below:

enter image description here

Which means the sum of order number and dollar revenue split by order status for each territory.

I tried to use pivot twice, one for sum of order number and one for dollar revenue but it looks like i can't use the same column in more than a pivot

SELECT 
    Territory, [RELEASED], [UNRELEASED] 
FROM 
    @Territory_Month
PIVOT
    (SUM([count Order Number]) 
        FOR [Order Status] IN ([RELEASED], [UNRELEASED])) piv1
PIVOT 
    (SUM([sum Dollar Revenue]) 
        FOR [Order Status] IN ([RELEASED], [UNRELEASED])) piv2 
ORDER BY 
    Territory

Any suggestions please ? Thank you.

Upvotes: 0

Views: 75

Answers (2)

Mukesh Arora
Mukesh Arora

Reputation: 1813

By changing the column name & value you can use multiple columns with Pivot like below

Try This

DECLARE @Territory_Month TABLE 
    (Territory varchar(20), [Order Status] varchar(20), [count Order Number] int,[sum Dollar Revenue] float);

INSERT INTO @Territory_Month
(Territory, [Order Status], [count Order Number], [sum Dollar Revenue])
VALUES
('BENELUX','RELEASED',108,20363879.11),
('CENTRAL EUROPE','RELEASED',615,11502633.14),
('EAST EUROPE','RELEASED',292,4689655.19),
('FRANCE','RELEASED',228,5333825.69),
('IBERIA','RELEASED',82,1985003.46),
('ITALY & ISRAEL','RELEASED',211,3844900.74),
('META','RELEASED',173,3478765.18),
('NORDICS','RELEASED',132,4017755.1252),
('SEE','RELEASED',326,2211346.28),
('UKI','RELEASED',124,17541413.6),
('BENELUX','UNRELEASED',33,505935.16),
('CENTRAL EUROPE','UNRELEASED',111,597457.364505),
('EAST EUROPE','UNRELEASED',96,2411721.25),
('FRANCE','UNRELEASED',41,466737.3675),
('IBERIA','UNRELEASED',20,112696.37),
('ITALY & ISRAEL','UNRELEASED',129,928122.01),
('META','UNRELEASED',156,771615.44),
('NORDICS','UNRELEASED',34,778853.82),
('SEE','UNRELEASED',81,260567.31),
('UKI','UNRELEASED',115,6335067.356471);


Select * From
(
SELECT Territory, [Order Status] OrderStatus1,  [Order Status] + '2' OrderStatus2, [count Order Number], [sum Dollar Revenue]
FROM @Territory_Month ) as tbl
PIVOT ( SUM([count Order Number]) FOR [OrderStatus1]   in ([RELEASED],[UNRELEASED])) piv1
PIVOT ( SUM([sum Dollar Revenue]) FOR [OrderStatus2]  in ([RELEASED2],[UNRELEASED2])) piv2 
ORDER BY Territory

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Your image doesn't work -- another reason why text tables are so helpful.

But I think you want something like this:

select territory,
        sum(case when status = 'RELEASED' then 1 else 0 end) as num_released,
        sum(case when status = 'RELEASED' then rev else 0 end) as rev_released,
        sum(case when status = 'UNRELEASED' then 1 else 0 end) as num_unreleased,
        sum(case when status = 'UNRELEASED' then rev else 0 end) as rev_unreleased
 from @Territory_Month tm
 group by territory;

Here is a db<>fiddle.

Notice that I changed the column names so they don't have to be escaped. That makes it easier to write and read queries.

Upvotes: 1

Related Questions