How to sort or order date in dynamic Pivot Sql Server 2008

Question

I have the following table

enter image description here

The table after pivoting is as follows. How do I maintain the date order in pivoted columns?

enter image description here

It depends on how you pivot data. In any case, at some point you should sort/order DATE values as datetime/date. I think at this moment the values of DATE column are strings/varchar. — Bogdan Sahlean
– Bogdan Sahlean, Commented Feb 21, 2014 at 5:49
The date is getting ordered with your query, but how to bring in the same format with slash 01/jan/2014 etc? — Sarath Subramanian
– Sarath Subramanian, Commented Feb 21, 2014 at 9:42

Zerotoinfinity · Accepted Answer · 2014-02-21 05:37:52Z

1

Here is how you can do it dynamically.

    CREATE TABLE TEST
(
  NAME VARCHAR(10),
  DATECOL DATE,
  VALUECol INT   
)

INSERT INTO TEST
VALUES
('A', '01/JAN/2014', 10),
('B', '01/JAN/2014', 20),
('A', '26/JAN/2014', 20),
('B', '26/JAN/2014', 30),
('A', '01/FEB/2014', 40),
('B', '01/FEB/2014', 50),
('A', '26/FEB/2014', 60),
('B', '26/FEB/2014', 70)


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

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(DATECOL) 
                    from TEST
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select *
      from
      (
        select NAME,DATECOL,VALUECOL
        from TEST

      ) x1
      pivot
      (
        max(VALUECOL)
        for DATECOL in ('+ @colspivot +')
      ) p'

    exec(@query)

Check this on SQL Fiddle

edited Feb 21, 2014 at 5:37

answered Feb 21, 2014 at 5:30

Zerotoinfinity

6,57834 gold badges139 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sarath Subramanian · Accepted Answer · 2014-11-22 09:06:12Z

I got my own way to resolve this problem

CREATE TABLE #TEMP (Name varchar(10), [DATE] datetime, Value int)
INSERT #TEMP VALUES 
('A','01/JAN/2014',10), 
('B','01/JAN/2014',20), 
('A','26/JAN/2014',20), 
('B','26/JAN/2014',30), 
('A','01/FEB/2014',40), 
('B','01/FEB/2014',50), 
('A','26/FEB/2014',60),
('B','26/FEB/2014',70)

Now we assign date in the format DD/MMM/YYYY to the variable (Note: The [DATE] column should be of the type datetime/date)

DECLARE @cols NVARCHAR (MAX)

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

After pivot the columns will be in perfect Date order with the format DD/MMM/YYYY

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT Name,REPLACE(CONVERT(NVARCHAR, [DATE], 106), '' '', ''/'') [DATE] , value FROM #TEMP
             ) x
             PIVOT 
             (
                 SUM(value)
                 FOR [DATE] IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Like this you can convert to any date formats and can maintain the order of date in pivoted columns by editing the conversion type in SELECT @cols and inner SELECT of Pivot statement

Collectives™ on Stack Overflow

How to sort or order date in dynamic Pivot Sql Server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related