SQL将行移动为列-PIVOT查询[重复]

Question

鉴于：

+-----------+---------------+-------------+-------+
|   Name    |   Location    | Description | Value |
+-----------+---------------+-------------+-------+
| Company A | Houston       | Sales       |   100 |
| Company A | Houston       | Profit      |    50 |
| Company B | San Francisco | Sales       |   500 |
| Company B | San Francisco | Profit      |   200 |
| Company C | New York      | Sales       |   200 |
| Company C | San Francisco | Profit      |   150 |
+-----------+---------------+-------------+-------+

如何使用SQL将其转换为：

+----------+---------------+--------+---------+
|   Name   |   Location    | Sales  | Profit  |
+----------+---------------+--------+---------+
| CompanyA | Houston       |    100 |      50 |
| CompanyB | San Francisco |    500 |     200 |
| CompanyC | New York      |    200 |     150 |
+----------+---------------+--------+---------+

Answer 1

要将数据转换为所需的形式，您可以执行以下操作：

 SELECT NAME
          ,LOCATION
          ,SUM (CASE WHEN DESCRIPTION = 'Sales' THEN Values ELSE 0 END) AS SALES
          ,SUM (CASE WHEN DESCRIPTION = 'Profit' THEN Values ELSE 0 END) AS PROFIT
 FROM yourtable
 GROUP BY NAME
               ,LOCATION

Answer 2

假设您的描述值仅为销售和利润，这里有一个CTE解决方案。我在前面调用了初始表。

with cteSales as (select name, value from Before where description = 'Sales')
, cteProfit as (select name, value from Before where description  = 'Profit')
select 
distinct 
    name
    , location
    , (select value from cteSales where name = B.name) as Sales
    , (select value from cteProfit where name = B.name) as Profit
from Before B

Answer 3

尝试1或2查询：http://sqlfiddle.com/#！6/bb33b/7
第二个给出了您的问题中显示的确切结果，但我猜第一个是您正在寻找的。

SELECT Name, Location,
       Sum( Case Description when 'Sales' Then Value else 0 end ) Sales,
       Sum( Case Description when 'Profit' Then Value else 0 end ) Profit
FROM table1
GROUP BY Name, Location
Order by 1,2
;

|      Name |      Location | Sales | Profit |
|-----------|---------------|-------|--------|
| Company A |       Houston |   100 |     50 |
| Company B | San Francisco |   500 |    200 |
| Company C |      New York |   200 |      0 |
| Company C | San Francisco |     0 |    150 |

SELECT Name, Min( Location ) As Location,
       Sum( Case Description when 'Sales' Then Value else 0 end ) Sales,
       Sum( Case Description when 'Profit' Then Value else 0 end ) Profit
FROM table1
GROUP BY Name
Order by 1,2
;
;

|      Name |      Location | Sales | Profit |
|-----------|---------------|-------|--------|
| Company A |       Houston |   100 |     50 |
| Company B | San Francisco |   500 |    200 |
| Company C |      New York |   200 |    150 |