Arian Arian - 3 months ago 8
SQL Question

Grouping Subsidiary Values in one groups in PIVOT

Please consider this scenario:

We have Order table that store orders specifications. One of these specification is currency. I have several currency in that table such as Dollar, Euro, Pound, Yen, Dirham, Rial, Rupee. Dollar, Euro, Pound are main currencies. I want to group others in 1 group in my query. How I can do this using

PIVOT
command?

I want to create this such result:

ProductCategory Dollar Euro Pound Others
---------------------------------------------------------------
P1 1000 2000 500 880
P2 1200 100 200 750
P3 1900 8000 700 640
P4 1500 500 1000 270
P5 2000 1700 1200 1000


thanks




Update 1)

My source table is like:

ProductCategory Currency Amount
----------------------------------------
P1 Dollar 100
P2 Euro 500
P3 Dollar 100
P4 Yen 200
P1 Dollar 100
P3 Rupee 50
...





Update 2)

declare @tbl table
(
Product nvarchar(50),
Cur nvarchar(50),
Amount int
)


insert into @tbl values
('p1', 'dollar',100),
('p2', 'euro',100),
('p3', 'pound',100),
('p1', 'a',100),
('p1', 'b',100),
('p2', 'c',100),
('p3', 'dollar',100),
('p3', 'euro',100),
('p2', 'euro',100),
('p2', 'euro',100),
('p1', 'j',100)


SELECT pv.Product
,ISNULL(pv.Dollar,0) AS [Dollar]
,ISNULL(pv.Euro,0) AS [Euro]
,ISNULL(pv.Pound,0) AS [Pound]
,ISNULL(pv.Other,0) AS [Other]
FROM (
SELECT *
,CASE
WHEN [Cur] NOT IN ('dollar','euro','pound') THEN 'Other'
ELSE [Cur]
END AS [newCurrency]
FROM @tbl
)as a
PIVOT (
SUM([Amount])
FOR [newCurrency] IN ([dollar],[euro],[pound],[Other])
)as pv


and get this result:

enter image description here

Answer

You were almost there. Modified your query little a bit in Update 2. I think this is what you want.

declare @tbl  table
(
    Product   nvarchar(50),
    Cur       nvarchar(50),
    Amount    int
)

insert into @tbl values
('p1', 'dollar',100),
('p2', 'euro',100),
('p3', 'pound',100),
('p1', 'a',100),
('p1', 'b',100),
('p2', 'c',100),
('p3', 'dollar',100),
('p3', 'euro',100),
('p2', 'euro',100),
('p2', 'euro',100),
('p1', 'j',100)

SELECT pv.Product
    ,sum(ISNULL(pv.Dollar,0)) AS [Dollar]
    ,sum(ISNULL(pv.Euro,0)) AS [Euro]
    ,sum(ISNULL(pv.Pound,0)) AS [Pound]
    ,sum(ISNULL(pv.Other,0)) AS [Other]
FROM (
SELECT *
    ,CASE
        WHEN [Cur] NOT IN ('dollar','euro','pound') THEN 'Other'
        ELSE [Cur]
    END AS [newCurrency] 
FROM @tbl
)as a
PIVOT (
    SUM([Amount]) 
    FOR [newCurrency] IN ([dollar],[euro],[pound],[Other])
)as pv
group by product
Comments