Pivot with dynamic dates as column headers

I have a table with data structure like:

Entity_ID   Product    docDate            Volume
1                    G            2010-09-01          500
1                    G            2010-09-02          750
1                   G             2010-09-03          600
2                    G            2010-09-01          900
2                    G            2010-09-02          850
2                   G             2010-09-03          875

I want to create a SQL Server stored procedure which will accept @StartDate and @EndDate data parameters and return the data in the format

Entity_ID   09/01/14     09/02/14    09/03/14
1                    500                570             600
2                    900                850             875

I know this will involve reformatting the docDate column, but I'm still struggling with the syntax to do this in SQL Server, after so many years of the simplicity of doing it in Access!
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Auerelio VasquezETL DeveloperCommented:
I might do this with a common table expression first. something like this


create proc sp_pivot
@startDate datetime, @endDate dateTime

As

with cte (select Entity_ID, Product, DocDate, Volume from table where docDate between @startDate and @EndDate)


then use the CTE and pivot on docDate
0
Sytech SolutionsCommented:
Hi there

This should do the trick:

CREATE PROCEDURE [dbo].[StoredProcedureName]

@StartDate DATETIME,
@EndDate DATETIME

AS

SET NOCOUNT ON;

SET DATEFORMAT mdy;

DECLARE @docDates VARCHAR(MAX);

SELECT @docDates = COALESCE(@docDates + '],[', '') +
				  (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))
FROM
(
	SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
	WHERE [docDate] BETWEEN @StartDate AND @EndDate
) AS [DistinctDocDates];

DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT * FROM (SELECT [Entity_ID],[docDate],[Volume] FROM [dbo].[Table_1]) AS [SubTable]
PIVOT
(
     MAX([Volume])
FOR [docDate] IN ([' + @docDates + '])
) AS [Pivot];';

EXEC sp_executesql @sql;

Open in new window


 Hope this helps
0
Dale FyeAuthor Commented:
SyTech,

Can you explain what you are doing, so that I gain knowledge here?

DECLARE @docDates VARCHAR(MAX);

SELECT @docDates = COALESCE(@docDates + '],[', '') +
	  (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))
FROM
(
	SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
	WHERE [docDate] BETWEEN @StartDate AND @EndDate
) AS [DistinctDocDates];

Open in new window

This declares a string variable and is somehow creating a single string composed of the date values formatted as [mm/dd/yy] and separated by commas.  I see that you are adding the opening "[" and closing "]" later in the code.  I just don't understand how this statement results in a single text string, when the query inside the ( ) is returning a recordset of N dates.

I get the rest of the dynamic SQL String, just don't understand this code segment.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sytech SolutionsCommented:
Yep no worries :)

The format of the pivot in SQL needs row values to convert to column names in the pivot FOR clause which syntactically should be surround with the square brackets, for example:

SELECT [columnName] FROM [table];

The COALESCE function is the one used to concatenate all the values from the [docDate] row together (appending them to the string variable). I am adding ],[ to the string value with each [docDate] which acts as a delimiter. As such it isn't added before and after all the values of [docDate] - hence my reason for adding the first opening and final closing square brackets later on as you've noticed.

I added the COALESCE function to cope with growing data within your table, if on the other hand you are only ever going to have the rows in your example you could hard-code the [docDate] values in the pivot and do away with the COALESCE altogether as follows:

SELECT * FROM
(
	SELECT [Entity_ID],
		   (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2)) AS [docDate],
		   [Volume] FROM [dbo].[Table_1]
) AS [SubTable]
PIVOT
(
     MAX([Volume])
FOR [docDate] IN ([09/01/10], [09/02/10], [09/03/10])
) AS [Pivot];

Open in new window


If the pivot column names were simple strings (e.g [docDate] contained data such as 'Jan','Feb','Mar' etc.) then you wouldn't need the square brackets, however the /'s in the [docDate] values would cause errors without the brackets.  

Hope that goes someway to explaining it, let me know if you have any more questions.
0
Dale FyeAuthor Commented:
Still not quite there.

How does this statement:
SELECT @docDates = COALESCE(@docDates + '],[', '') +
	  (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))

Open in new window

concatenate all of the distinct records returned by this:
(
	SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
	WHERE [docDate] BETWEEN @StartDate AND @EndDate
) 

Open in new window

into a single value?

In Access, I would write a loop to concatenate all of those values (if I had to do that, which you don't in an Access CrossTab query), so I assumed you would need some form of cursor operation to do that in SQL Server.
0
Dale FyeAuthor Commented:
One more question.

How do I get the dates returned in the proper order (increasing left to right)?

I tried adding an Order By clause to this statement:
(
      SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
      WHERE [docDate] BETWEEN @StartDate AND @EndDate
        ORDER BY [docDate]
)
 and received the following error message:

Msg 1033, Level 15, State 1, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
0
Sytech SolutionsCommented:
In a simplified way (so stripping out all extra functions) we are essentially doing this:

DECLARE @stringVariable VARCHAR (MAX);
SET @stringVariable = '';
SELECT @stringVariable = @stringVariable + [column] FROM [table];

Open in new window


the key is the @stringVariable = @stringVariable + [column] part, selecting each value and appending it to the @stringVariable itself.

In the original stored procedure The COALESCE part handles NULL values from the [docDate] column as well as variable initiation.

Hope that helps?
0
Sytech SolutionsCommented:
Ok in order to do the ordering as you've noticed you can't have ORDER BY in sub-queries, so I've updated the procedure to move the DISTINCT [docDates] into a table variable. This then eliminates the sub-query in the concatenation, which means you can add an ORDER BY clause as required at that point. The order-by then bubbles up to the pivot.

So the final result is:

ALTER PROCEDURE [dbo].[StoredProcedureName]

@StartDate DATETIME,
@EndDate DATETIME

AS

SET NOCOUNT ON;

SET DATEFORMAT mdy;

DECLARE @docDates VARCHAR(MAX);

DECLARE @docDatesTable TABLE
(
	[docDate] DATETIME
);

INSERT INTO @docDatesTable SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
WHERE [docDate] BETWEEN @StartDate AND @EndDate;

SELECT @docDates = COALESCE(@docDates + '],[', '') +
	  (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))
FROM @docDatesTable ORDER BY [docDate] DESC;

DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT * FROM (SELECT [Entity_ID],[docDate],[Volume] FROM [dbo].[Table_1]) AS [SubTable] 
PIVOT
(
     MAX([Volume])
FOR [docDate] IN ([' + @docDates + '])
) AS [Pivot];';

EXEC sp_executesql @sql;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeAuthor Commented:
So,

What do they call that syntax where you did the:

(
      SELECT DISTINCT [docDate] FROM [dbo].[Table_1]
      WHERE [docDate] BETWEEN @StartDate AND @EndDate
)

Open in new window

0
Dale FyeAuthor Commented:
Appreciate the help, and especially the explanation.

I see you are a newbie to EE,  welcome to the community.
0
Sytech SolutionsCommented:
That syntax I generally call a sub-query or sub-select. Thanks for the welcome + accepted answer :)
0
Dale FyeAuthor Commented:
Follow-up question here
0
PortletPaulfreelancerCommented:
Hi, just wanted to propose a few items

While coalesce can be used to concatenate the fields, a more conventional approach is to use "for xml path" and when you use this order by is permitted. STUFF() is also used but only to remove the leading comma.

In addition, if you choose DD/MM/YY as your wanted heading format, then style number 1 will provide that without needing left() and year() etc.

Finally it is a good idea to use QUOTENAME() as it handles all sorts of exceptions gracefully.

if you run this as a query only as far as select @docDates you will see this produced:
[09/01/10], [09/02/10], [09/03/10]

and because it comes with leading & trailing [ ] then line 25 is adjusted accordingly

declare @StartDate DATETIME = '2010-09-01'
declare @EndDate DATETIME = '2010-09-03'

DECLARE @docDates VARCHAR(MAX)


SELECT @docDates = STUFF(
                          (
                            SELECT DISTINCT ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1)) -- style 1 is MM/DD/YY
                            FROM [Table_1]
                            WHERE [docDate] BETWEEN @StartDate AND @EndDate
                            ORDER BY ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1))
                            FOR XML PATH ('')
                          )
                          , 1, 1, '')

--select @docDates

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM (SELECT [Entity_ID],[docDate],[Volume] FROM [dbo].[Table_1]) AS [SubTable]
PIVOT
(
     MAX([Volume])
FOR [docDate] IN (' + @docDates + ')
) AS [Pivot];'

EXEC sp_executesql @sql
;

Open in new window

for other date formats see: SQL Server Date Styles (formats) using CONVERT()

see: http://sqlfiddle.com/#!3/e0e10/1
    CREATE TABLE Table_1
    	([Entity_ID] int, [Product] varchar(1), [docDate] datetime, [Volume] int) 
    ;
    	
    INSERT INTO Table_1
    	([Entity_ID], [Product], [docDate], [Volume])
    VALUES
    	(1, 'G', '2010-09-01 00:00:00', 500),
    	(1, 'G', '2010-09-02 00:00:00', 750),
    	(1, 'G', '2010-09-03 00:00:00', 600),
    	(2, 'G', '2010-09-01 00:00:00', 900),
    	(2, 'G', '2010-09-02 00:00:00', 850),
    	(2, 'G', '2010-09-03 00:00:00', 875)
    ;

**Query 1**:

    declare @StartDate DATETIME = '2010-09-01'
    declare @EndDate DATETIME = '2010-09-03'
    
    DECLARE @docDates VARCHAR(MAX)
    
    
    SELECT @docDates = STUFF(
                              (
                                SELECT DISTINCT ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1))
                                FROM [Table_1]
                                WHERE [docDate] BETWEEN @StartDate AND @EndDate
                                ORDER BY ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1))
                                FOR XML PATH ('')
                              )
                              , 1, 1, '')
    
    select @docDates
    

**[Results][2]**:
    
    |                            COLUMN_0 |
    |-------------------------------------|
    |  [09/01/10], [09/02/10], [09/03/10] |


**Query 2**:

    declare @StartDate DATETIME = '2010-09-01'
    declare @EndDate DATETIME = '2010-09-03'
    
    DECLARE @docDates VARCHAR(MAX)
    
    
    SELECT @docDates = STUFF(
                              (
                                SELECT DISTINCT ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1))
                                FROM [Table_1]
                                WHERE [docDate] BETWEEN @StartDate AND @EndDate
                                ORDER BY ', ' + QUOTENAME(CONVERT(VARCHAR, [docDate], 1))
                                FOR XML PATH ('')
                              )
                              , 1, 1, '')
    
    --select @docDates
    
    DECLARE @sql NVARCHAR(MAX)
    
    SET @sql = 'SELECT * FROM (SELECT [Entity_ID],[docDate],[Volume] FROM [dbo].[Table_1]) AS [SubTable]
    PIVOT
    (
         MAX([Volume])
    FOR [docDate] IN (' + @docDates + ')
    ) AS [Pivot];'
    
    EXEC sp_executesql @sql
    

**[Results][3]**:
    
    | ENTITY_ID | 09/01/10 | 09/02/10 | 09/03/10 |
    |-----------|----------|----------|----------|
    |         1 |      500 |      750 |      600 |
    |         2 |      900 |      850 |      875 |



  [1]: http://sqlfiddle.com/#!3/e0e10/1

Open in new window

0
Dale FyeAuthor Commented:
Thanks, Paul.  I'll take a look at that syntax.
0
Dale FyeAuthor Commented:
Paul,

I like that Stuff function, think I'll create one of those for use in VBA.

There was something in the Help file about 'FOR XML' being deprecated but I'm not at all familiar with XML, so it went over my head.
0
PortletPaulfreelancerCommented:
that deprecation notice does not affect  FOR ... XML ... PATH
0
Dale FyeAuthor Commented:
I like that technique, much simpler than creating the temp table, and very straight forward.
0
PortletPaulfreelancerCommented:
:) that's why I  mentioned it, glad you like it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.