SQL to convert distinct item in row to column

问题:

I have a table as

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

I want below output

Date        Mango       Orange
20170101    5           6
20170102    7           8

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

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.

like

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

then output should be as ...

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

回答1:

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

Result:

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

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

回答2:

IF OBJECT_ID('Tempdb..#Temp') IS NOt NUll
Drop Table #Temp

;With cte([Date] ,Item ,Quantity)
AS
(
SELECT '20170101','Mango'  ,5 Union all
SELECT '20170101','Orange' ,6 Union all
SELECT '20170102','Mango'  ,7 Union all
SELECT '20170102','Orange' ,8 Union all
SELECT '20170102','Cherry' ,9
)
SELECT * INTO #Temp FROM cte

DECLARE @dynamicCol nvarchar(max),
        @Sql nvarchar(max),
        @dynamicCol2 nvarchar(max)

SELECT @dynamicCol=STUFF((SELECT DISTINCT ', ' + 'ISNULL('+Item +',''0'')  AS '+ Item  FROM  #Temp
FOR XML PATH('')),1,1,'')

SELECT @dynamicCol2=STUFF((SELECT DISTINCT ', ' + Item  FROM  #Temp
FOR XML PATH('')),1,1,'')


SET @Sql='
            SELECT [Date] , '+ @dynamicCol +' From
            (
            SELECT [Date] ,Item ,Quantity From
            #temp
            )AS Src
            PIVOT 
            (
            MAX([Quantity]) For [Item ] IN ('+@dynamicCol2+')
            )
            AS Pvt
            '

PRINT @Sql

EXEC(@Sql)

OutPut

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

回答3:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Item) 
            FROM dbo.OrderTable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DateOrdered, ' + @cols + ' from 
            (
                select Item, DateOrdered, Quantity
                from dbo.OrderTable
           ) x
            pivot 
            (
                 Sum(Quantity)
                for Item in (' + @cols + ')
            ) p '
execute(@query)

If you do this it will give you result that you want

回答4:

Try this :

CREATE TABLE [dbo].[test](
    [Date] [int] NULL,
    [Item] [nvarchar](50) NULL,
    [Quantity] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170101, N'Mango', 5)
GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170101, N'Orange', 6)
GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170102, N'Mango', 7)
GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170102, N'Orange', 8)
GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170102, N'Cherry', 5)
GO
INSERT [dbo].[test] ([Date], [Item], [Quantity]) VALUES (20170103, N'Cherry', 2)
GO

dynamic sql :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Item) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT DATE, ' + @cols + N' from 
             (
                select Date,Item, Quantity from test
            ) x
            pivot 
            (
                max(Quantity)
                for Item in (' + @cols + N')
            ) p '

exec sp_executesql @query;

result :

DATE       Cherry   Mango   Orange
20170101    NULL    5       6
20170102    5       7       8
20170103    2      NULL     NULL