How to Pivot Dynamically with Date as Column

How to pivot dynamically with date as column

Here is your sample table

SELECT * INTO #Names
FROM
(
SELECT 1 ID,'ITEM1' NAME 
UNION ALL
SELECT 2 ID,'ITEM2' NAME 
)TAB

SELECT * INTO #Stockdates
FROM
(      
SELECT 1 ID,1 NAMEID,8 STOCK,'2-1-2014 ' [DATE]
UNION ALL
SELECT 2 ID,2 NAMEID,2 STOCK,'4-1-2014 ' [DATE]
)TAB

Put the join data to a temperory table

SELECT N.NAME,S.[DATE],S.STOCK 
INTO #TABLE
FROM #NAMES N
JOIN #Stockdates S ON N.ID=S.NAMEID

Get the columns for pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', 
               '[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
               FROM    (SELECT DISTINCT [DATE] FROM #TABLE) PV  
               ORDER BY [DATE]

Now pivot it

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT * FROM 
             (
                 SELECT * FROM #TABLE
             ) x
             PIVOT 
             (
                 SUM(STOCK)
                 FOR [DATE] IN (' + @cols + ')
            ) p      

            '     
EXEC SP_EXECUTESQL @query

And your result is here

Dynamic Pivot Table with date column

I was able to get it with the code below, you just need to replace #table with your table name. Also ignore the first part of the code that sets up the table.

There are similar questions/answers here: SQL Server dynamic PIVOT query?

-------------------------------------------------------------------
IF OBJECT_ID('tempdb..#table') IS NOT  NULL
BEGIN
    DROP TABLE #table
END

CREATE TABLE #table(ITEMCODE VARCHAR(10),DATE date,INSTOCK int)

insert into #table values('ABC001','2019-01-04',10)
insert into #table values('ABC001','2019-02-04',10)
insert into #table values('ABC001','2019-03-04',10)
insert into #table values('ABC001','2019-04-04',5)
insert into #table values('ABC001','2019-05-04',5)
-------------------------------------------------------------------

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.DATE) 
            FROM #table c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ITEMCODE, ' + @cols + ' from 
            (
                select ITEMCODE
                    , DATE
                    , INSTOCK
                from #table
           ) x
            pivot 
            (
                 sum(INSTOCK)
                for DATE in (' + @cols + ')
            ) p '

execute(@query)

SQL Server- PIVOT Table to one column per date

You can try to use dynamic SQL for BalDate.

due to your query has parameter so you can try to use sp_executesql and append parameters in the execute syntax.

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(a1.BalDate) 
            FROM IDTable a1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
FROM (
    SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 
    FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
    WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year
) as d1
PIVOT (
    SUM(Balance)
    For Balance in ('+ @cols +') --Error: see below
) piv'

EXECUTE sp_executesql @query, N'@year INT,@month INT', 
                    @year = @year,
                    @month = @month

Dynamic Pivot Queries with dynamic dates as column header in SQL Server

Try this query..it will help you

select * into #tempp from(

select '2016-01-17' as DATES,100 ORDERS,50 CANCELED_ORDERS
UNION ALL
SELECT '2016-01-18',120,20
UNION ALL
SELECT '2016-01-20',150,30
)AS A

--SELECT * FROM #tempp

declare @pivotcols nvarchar(max),@unpivotcols nvarchar(max),@SQLQUERY NVARCHAR(MAX)
select @pivotcols=stuff((select ','+quotename(dates) from #tempp for xml path('')),1,1,'')
--select @pivotcols

select @unpivotcols=stuff((select ','+name from tempdb.sys.columns where object_id =
object_id('tempdb..#tempp') and name<>'DATES' for xml path('')),1,1,'')
--select @unpivotcols

SET @SQLQUERY=N'select * from (
SELECT * FROM #tempp
)as a
unpivot (AMOUNTS FOR Dates  in ('+@unpivotcols+N') 
) AS UNPI
PIVOT (MAX(AMOUNTS) FOR DATES IN ('+@pivotcols+N')
)AS A'

PRINT @SQLQUERY

EXEC SP_EXECUTESQL @SQLQUERY

The output will be like.

    +-----------------+------------+------------+------------+
    |      Dates       | 2016-01-17 | 2016-01-18 | 2016-01-20|
    +-----------------+------------+------------+------------+
    | CANCELED_ORDERS |         50 |         20 |         30 |
    | ORDERS          |        100 |        120 |        150 |
    +-----------------+------------+------------+------------+

SQL Pivot on Dynamic Date Range

You can use ORDER BY in your first SELECT statement between the FROM and the FOR XML. However, exactly which expression to use in the order by may depend on the data type of column ColumnDate. If it is a date column, then you can just use

ORDER BY ColumnDate

dynamic pivot with parameter passed in

exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd

---

Related Topics