基于分组返回列的 SQL 查询 [英] SQL query with return columns based on grouping

问题描述

我有这个选择如下:

SELECT SUBSTRING(Col1, 1, 3)          AS 'Series',
       b.Col2,
       CAST(c.Price * c.Qty AS MONEY) AS Total
FROM   tableName a
inner join ....
...
WHERE  DATE BETWEEN '1/1/2012' AND '1/31/2012'
       AND B IN ( 'C50', 'C51', 'C52', 'C53', 'C54' )
GROUP  BY Col1,
          b.Col2,
          c.Price,
          c.Qty  

返回这个结果集:

Series       Col2        Total
----------   ----------  ----------
105          C50         5.00
105          C50         15.00
105          C53         20.00
105          C53         20.00
105          C53         20.00

这就是我希望它的工作方式:

And this is how I would like it to work:

Series       C50         C53
----------   ----------  ----------
105          20.00       60.00   

不确定如何进行内部选择以获得该结果.有什么想法吗?

Not sure how to do the inner selects to get that result. Any ideas?

推荐答案

请尝试以下代码.您必须使用 PIVOT 查询.

Please try the following codes. You have to use PIVOT query.

create table TableName (Series varchar(20),col2 varchar(10), price decimal(8,2))

insert into TableName values ('105','C50',30)
insert into TableName values ('105','C50',10)

insert into TableName values ('105','C53',20)
insert into TableName values ('105','C53',30)


SELECT Series,
       [C50],
       [C53]
FROM   (SELECT Series,
               col2,
               price
        FROM   TableName) AS SourceTable 
                   PIVOT ( SUM(price) FOR col2 IN ([C50],
                                                   [C53])
       ) AS PivotTable;  

这篇关于基于分组返回列的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！