Solved

Pivot with dynamic dates as column headers

Posted on 2014-09-11
18
3,008 Views
Last Modified: 2014-09-12
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!
0
Comment
Question by:Dale Fye (Access MVP)
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 1

Expert Comment

by:basile
ID: 40317216
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
 
LVL 2

Expert Comment

by:Sytech Solutions
ID: 40317224
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40317388
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
 
LVL 2

Expert Comment

by:Sytech Solutions
ID: 40317494
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40317536
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40317563
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
 
LVL 2

Expert Comment

by:Sytech Solutions
ID: 40317575
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
 
LVL 2

Accepted Solution

by:
Sytech Solutions earned 500 total points
ID: 40317637
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40317690
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40317697
Appreciate the help, and especially the explanation.

I see you are a newbie to EE,  welcome to the community.
0
 
LVL 2

Expert Comment

by:Sytech Solutions
ID: 40317701
That syntax I generally call a sub-query or sub-select. Thanks for the welcome + accepted answer :)
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40317840
Follow-up question here
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318786
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40318955
Thanks, Paul.  I'll take a look at that syntax.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40318995
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40319174
that deprecation notice does not affect  FOR ... XML ... PATH
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40319421
I like that technique, much simpler than creating the temp table, and very straight forward.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40319592
:) that's why I  mentioned it, glad you like it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now