SQL将行中的不同项目转换为列 [英] SQL to convert distinct item in row to column

问题描述

我有一张桌子

Date          Item          Quantity
20170101      Mango         5
20170101      Orange        6
20170102      Mango         7
20170102      Orange        8

我想要下面的输出

Date        Mango       Orange
20170101    5           6
20170102    7           8

为此，我在sql查询下使用了

for this i used below sql query

SELECT 
   Date,
   SUM(case when Item='Mango' then Quantity else 0 end) AS Mango,
   SUM(case when Item='Orange' then Quantity else 0 end) AS Orange
FROM orderTable
GROUP BY date

但这是对Mango和Orange的硬编码.如果我需要orderTable中的新商品该怎么办.任何人都可以建议我如何使该查询动态化.这样一来，如果我添加新商品，它会自动创建一个以商品名称作为名称的新column，当​​未为该商品下订单时，该商品的日期将在列下显示0值.

but this is kind of hard coding for Mango and Orange. What if I need new item in orderTable. Can any one suggest me how can I make this query dynamic. So that if I add new item it automatically create new coulmn having item name as name and it will have 0 value under column against date when order not placed for that item.

喜欢

Date          Item          Quantity
20170101      Mango         5
20170101      Orange        6
20170102      Mango         7
20170102      Orange        8
20170102      Cherry        9

然后输出应为...

Date        Mango       Orange    Cherry
20170101    5           6         0
20170102    7           8         9

推荐答案

IF OBJECT_ID('Test') IS NOt NUll
DROP TABLE Test

CREATE TABLE Test
(
    Date VARCHAR(100),
    Item VARCHAR(100),
    Quantity INT
)

INSERT Test VALUES
    ('20170101', 'Mango', 5),
    ('20170101', 'Orange', 6),
    ('20170102', 'Mango', 7),
    ('20170102', 'Orange', 8),
    ('20170102', 'Cherry', 9)

DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Columns AS VARCHAR(MAX)
DECLARE @Columns2 AS VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ',','') + QUOTENAME(Item)
FROM (SELECT DISTINCT Item FROM Test) AS B
ORDER BY B.Item

SELECT @Columns2 = COALESCE(@Columns2 + ',','') + 'ISNULL(' + QUOTENAME(Item) + ', 0) AS ' + Item
FROM (SELECT DISTINCT Item FROM Test) AS B
ORDER BY B.Item

SET @SQL = '
WITH PivotData AS
(
    SELECT Date, Item, Quantity FROM Test
)
SELECT
    Date, ' + @Columns2 + '
FROM PivotData
PIVOT
(
    SUM(Quantity)
    FOR Item
    IN (' + @Columns + ')
) AS PivotResult
ORDER BY Date'

EXEC(@SQL);

DROP TABLE Test

结果:

Date        Cherry  Mango   Orange
20170101    0       5       6
20170102    9       7       8

参考(代码图片未显示，但是如果您查看页面的源代码，则可以访问它): http://sqlmag.com/t-sql/pivoting-dynamic-way

Reference (code pic not shown but you can access it if you view source the page) : http://sqlmag.com/t-sql/pivoting-dynamic-way

这篇关于SQL将行中的不同项目转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！