CODEWITHSUNDEEP
sql-server
Eliseo Jr
Eliseo Jr

Reputation: 141

SQL Server Pivot and Sort

I have a shop order table with the Item code, description ReleaseDate and Required quantity. How to query in a pivot table format in such a way that the results will be pivoted using Year+Month of [ReleaseDate] in sorting order from oldest date to latest date. Using the Year+Month as a column.

This is my query but it fails.

 --Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT   @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME([YEARMONTH]) 
FROM (SELECT DISTINCT  CONVERT(char(6), cast([releaseddate] as date), 112 ) as [YEARMONTH] FROM [dbo].[ShopOrder]) as PivotQuery

SELECT   @PivotColumns

--Create the dynamic query with all the values for 
--pivot column at runtime

SET   @SQLQuery = 
    N'SELECT ItemCode, ' +   @PivotColumns + '
    FROM [dbo].[ShopOrder] 
    PIVOT( SUM(RequiredQty) 
          FOR [releaseddate] IN (' + @PivotColumns + ')) AS P'

SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

This is the original record

image

Results query must be like this

image

Upvotes: 2

Views: 3604

Answers (2)

shishir
shishir

Reputation: 851

Here I have tried to execute the pivot with your provided data.

Query

Select 
    [ItemCode],
    [Description],
    [2017/8],
    [2017/9]
from
(
select cast(year(ReleasedDate) as nvarchar)+'/'+cast(month(ReleasedDate) as nvarchar) as ReleasedDate,ItemCode,Description,RequiredQty
from shoporder) as PivotData
Pivot
(
sum(RequiredQty) for ReleasedDate in
([2017/8],[2017/9])) as Pivoting
order by ItemCode

Output:

enter image description here

Fiddle

Upvotes: 3

Deep
Deep

Reputation: 3202

You are not pivoting correctly and the column names should be generated in a single string. Try this:

CREATE TABLE ShopOrder (ItemCode VARCHAR(100),[Description] VARCHAR(100),ReleaseDate DATE, RequiredQty INT)
GO
INSERT INTO ShopOrder
VALUES
('A','SLEEVE NUT','08/01/2017',19200)
,('A','SLEEVE NUT','08/02/2017',18000)
,('A','SLEEVE NUT','09/01/2017',17000)
,('B','STARTER','08/03/2017',10000)
,('B','STARTER','08/04/2017',18000)
,('B','STARTER','09/15/2017',16000)

DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)

SET @PivotColumns = STUFF(( SELECT  DISTINCT ',[' + CONVERT(char(6), cast(ReleaseDate as date), 112 ) + ']'
                            FROM    ShopOrder
                            ORDER   BY ',[' + CONVERT(char(6), cast(ReleaseDate as date), 112 ) + ']'
                            FOR XML PATH('')),1,1,'')

SET   @SQLQuery = 
N'
SELECT ItemCode,'+ @PivotColumns + '
FROM (SELECT ItemCOde,CONVERT(char(6), cast(ReleaseDate as date),112) ReleaseDate, RequiredQty  
        FROM ShopOrder) AS T
PIVOT( SUM(RequiredQty)
        FOR ReleaseDate IN ('+@PivotColumns+')) AS P
'

SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

Upvotes: 2

Related Questions