Problem Using PIVOT for Dynamic Columns

I am following http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql and having some trouble getting the results I need.  I have a report that has an unknown number of columns and data for different locations.  I am trying to create columns for DISTINCT records.  I have a few problems I know of:

1) It is not pulling distinct records.  I am not telling it to, but I'm not sure where to place it.
2) Error:  Incorrect syntax near '09'.
3) Incorrect syntax near 'x'.
4) Lines 29 to 41 are incorrect.  Can someone explain exactly what it is supposed to be doing?.

ALTER PROCEDURE dbo.NEW
	@Location varchar(25)='POS147',
	@Begin date ='12/1/15' ,
	@End date ='12/30/15',
	@UserId varchar (75)='zzzz'
	AS
	BEGIN
		SET ARITHABORT
		ON
	
	/*http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql*/
	DECLARE @cols AS nvarchar(max),
	@query AS nvarchar(max)
	select @cols = STUFF((SELECT     Tenders.[Location Name] As Location, ',' + QUOTENAME(MediaType.MediaGroup) AS calccol, CONVERT(VARCHAR(10), Tenders.Date, 101) AS ShortDt, Tenders.Date, 
                      SUM(Tenders.Amount) AS Amount
FROM         MediaType INNER JOIN
                      Tenders INNER JOIN
                      Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
WHERE     (Tenders.Date >= @BEGIN) AND (Tenders.Date < DATEADD(dd, 1, @End)) AND (Locations.[Location Name] = @Location)
GROUP BY Tenders.[Location Name], ',' + QUOTENAME(MediaType.MediaGroup), CONVERT(VARCHAR(10), Tenders.Date, 101), Tenders.Date
HAVING      (Tenders.[Location Name] <> N'Cash')
ORDER BY Tenders.[Location Name]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                SELECT m.MediaGroup, SUM(Tenders.Amount) AS Amount
				FROM MediaType m INNER JOIN
                Tenders INNER JOIN
                Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
				GROUP BY m.MediaGroup    
            ) x
            pivot 
            (
                count(Amount)
                for MediaGroup in (' + @cols + ')
            ) p '

execute(@query)
	
END

	RETURN

Open in new window

rcowen00Asked:
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.

ste5anSenior DeveloperCommented:
The problem is how you're trying to build the column list. In this select you only select data from one column or expression:

DECLARE @Location VARCHAR(25) = 'POS147' ,
    @Begin DATE = '12/1/15' ,
    @End DATE = '12/30/15';
 
DECLARE @cols AS NVARCHAR(MAX) ,
    @query AS NVARCHAR(MAX);
SELECT  @cols = STUFF((SELECT   ',' + QUOTENAME(MT.MediaGroup) 
                       FROM     MediaType MT
                                INNER JOIN Tenders T ON MT.MediaType = T.Name
                                INNER JOIN Locations L ON T.[Location Name] = L.LocationNameOrig 
                       WHERE    ( T.[Date] >= @BEGIN )
                                AND ( T.[Date] < DATEADD(dd, 1, @End) )
                                AND ( L.[Location Name] = @Location )
                       GROUP BY QUOTENAME(MT.MediaGroup)
                       HAVING   ( T.[Location Name] <> N'Cash' )
                       ORDER BY T.[Location Name]
                       FOR XML PATH('') , TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = N'
WITH Data
          AS ( SELECT   MZ.MediaGroup ,
                        SUM(T.Amount) AS Amount
               FROM     MediaType MT
                        INNER JOIN Tenders T ON MT.MediaType = T.Name
                        INNER JOIN Locations L ON T.[Location Name] = L.LocationNameOrig
               GROUP BY m.MediaGroup
             )
    SELECT  *
    FROM    Data D PIVOT ( COUNT(Amount) FOR MediaGroup IN ( @cols ) ) P
';

SET @query = REPLACE(@query, '@cols', @cols);

EXECUTE(@query);

Open in new window


btw, having a different SELECT statement for calculating the columns like in your case looks wrong.
Mark WillsTopic AdvisorCommented:
Mark WillsTopic AdvisorCommented:
And agree with ste5an

You are initially building @cols with values from your first select, and what you need is only those (new) column names for subsequent use in the Pivot part. But you are mixing in other values as well

The Pivot will do the sorting out / aggregation over the new column names.

Interestingly, it almost looks as if the two queries are mixed up...

So, if wanting a bunch of unique mediagroup names to use as your column names in the pivot use :
DECLARE @cols AS nvarchar(max)

SELECT @cols = STUFF((SELECT     ',' + QUOTENAME(MediaType.MediaGroup) 
FROM   MediaType INNER JOIN
       Tenders INNER JOIN
       Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
WHERE     (Tenders.Date >= @BEGIN) 
AND (Tenders.Date < DATEADD(dd, 1, @End)) 
AND (Locations.[Location Name] = @Location)
AND (Tenders.[Location Name] <> N'Cash')
GROUP BY MediaType.MediaGroup
ORDER BY Tenders.[Location Name]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

Open in new window


Then the PIVOT part seems to be missing how you want the rows to be listed. As it stands, it will produce only 1 row, and probably need to add in something like Tenders.[Location Name] or maybe a date construct (such as week). But that is up to you (what we do know is it wont be any of the @ parameter values which will need to be strung in correctly).
DECLARE @query AS nvarchar(max)

SET @query = 'SELECT * from   -- can be * because everything else is already prepped
             (
                SELECT m.MediaGroup, Tenders.Amount AS Amount   -- maybe need to add in an extra column to define how rows will show
		FROM MediaType m INNER JOIN
                     Tenders INNER JOIN
                     Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON M.MediaType = Tenders.Name
                WHERE     (Tenders.Date >= @BEGIN) 
		AND (Tenders.Date < DATEADD(dd, 1, @End)) 
		AND (Locations.[Location Name] = @Location)
		AND (Tenders.[Location Name] <> N''Cash'')
            ) x
           PIVOT 
            (
               sum(Amount)   -- a count will show number of occurances not total amounts
               for MediaGroup in (' + @cols + ')
            ) p '

EXECUTE(@query)

Open in new window


Hope that helps explain it all a bit more... And please read http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html it does add more explanation and examples
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rcowen00Author Commented:
Thank you for your responses.  I am in process of reading the article suggested.  I did try plugging in the edited code and I'm getting "Incorrect syntax near ')'.

ALTER PROCEDURE dbo.SalesJournal_NEW
	@Location varchar(25)='POS147',
	@Begin date ='12/1/15' ,
	@End date ='12/30/15',
	@UserId varchar (75)='aaa'
	AS
	BEGIN
		SET ARITHABORT
		ON


DECLARE @cols AS nvarchar(max)

SELECT @cols = STUFF((SELECT     ',' + QUOTENAME(MediaType.MediaGroup) 
FROM   MediaType INNER JOIN
       Tenders INNER JOIN
       Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
WHERE     (Tenders.Date >= @BEGIN) 
AND (Tenders.Date < DATEADD(dd, 1, @End)) 
AND (Locations.[Location Name] = @Location)
AND (Tenders.[Location Name] <> N'Cash')
GROUP BY MediaType.MediaGroup
ORDER BY Tenders.[Location Name]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

DECLARE @query AS nvarchar(max)

SET @query = 'SELECT * from   
             (
                SELECT m.MediaGroup, Tenders.Amount AS Amount   
		FROM MediaType m INNER JOIN
                     Tenders INNER JOIN
                     Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON M.MediaType = Tenders.Name
                WHERE     (Tenders.Date >= @BEGIN) 
		AND (Tenders.Date < DATEADD(dd, 1, @End)) 
		AND (Locations.[Location Name] = @Location)
		AND (Tenders.[Location Name] <> N''Cash'')
            ) x
           PIVOT 
            (
               sum(Amount)   
               for MediaGroup in (' + @cols + ')
            ) p '

EXECUTE(@query)

Open in new window

ste5anSenior DeveloperCommented:
Don't modify your procedure. Run tests before. This would allow to use a PRINT @cols; for example to debug @cols or PRINT @query;

Then you can copy the query text into a new SSMS window and debug the actual code.
Mark WillsTopic AdvisorCommented:
while ste5an's advice is very good, I have to declare mea culpa.

I did not properly consider the data types and correct presentation of the variables (the @ parameters) in the second instance of the 'where' clause

ALTER PROCEDURE dbo.SalesJournal_NEW
	@Location varchar(25)='POS147',
	@Begin date ='12/1/15' ,
	@End date ='12/30/15',
	@UserId varchar (75)='aaa'
AS
BEGIN
	SET ARITHABORT ON


DECLARE @cols AS nvarchar(max)

SELECT @cols = STUFF((SELECT     ',' + QUOTENAME(MediaType.MediaGroup) 
FROM   MediaType INNER JOIN
       Tenders INNER JOIN
       Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
WHERE     (Tenders.Date >= @BEGIN) 
AND (Tenders.Date < DATEADD(dd, 1, @End)) 
AND (Locations.[Location Name] = @Location)
AND (Tenders.[Location Name] <> N'Cash')
GROUP BY MediaType.MediaGroup
--ORDER BY Tenders.[Location Name]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

DECLARE @query AS nvarchar(max)

SET @query = 'SELECT * from   
             (
                SELECT m.MediaGroup, Tenders.Amount AS Amount   
		FROM MediaType m INNER JOIN
                     Tenders INNER JOIN
                     Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON M.MediaType = Tenders.Name
                WHERE     (Tenders.Date >= ''' + convert(varchar(8),@BEGIN,112) + ''') 
		AND (Tenders.Date < DATEADD(dd, 1, '''+convert(varchar(8),@End,112)+''')) 
		AND (Locations.[Location Name] = '''+@Location+''')
		AND (Tenders.[Location Name] <> N''Cash'')
            ) x
           PIVOT 
            (
               sum(Amount)   
               for MediaGroup in (' + @cols + ')
            ) p '

EXECUTE(@query)

END
RETURN

Open in new window


You might still need the print commands and follow ste5ans advice on how to debug your code. I didn't get an error in the above code, but my quick and dirty test data might be entirely erroneous.

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
rcowen00Author Commented:
Mark Willis, I did get that to work once I changed the @Begin and @End to smalldatetype.  

ALTER PROCEDURE [dbo].[SalesJournal_NEW
	@Location varchar(25)='POS147',
	@Begin smalldatetime ='12/1/15' ,
	@End smalldatetime ='12/30/15',
	@UserId varchar (75)='aaa'
AS
BEGIN
	SET ARITHABORT ON


DECLARE @cols AS nvarchar(max)

SELECT @cols = STUFF((SELECT     ',' + QUOTENAME(MediaType.MediaGroup) 
FROM   MediaType INNER JOIN
       Tenders INNER JOIN
       Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON MediaType.MediaType = Tenders.Name
WHERE     (Tenders.Date >= @BEGIN) 
AND (Tenders.Date < DATEADD(dd, 1, @End)) 
AND (Locations.[Location Name] = @Location)
AND (Tenders.[Location Name] <> N'Cash')
GROUP BY MediaType.MediaGroup
--ORDER BY Tenders.[Location Name]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

DECLARE @query AS nvarchar(max)

SET @query = 'SELECT * from   
             (
                SELECT m.MediaGroup, Tenders.Amount AS Amount   
		FROM MediaType m INNER JOIN
                     Tenders INNER JOIN
                     Locations ON Tenders.[Location Name] = Locations.LocationNameOrig ON M.MediaType = Tenders.Name
                WHERE     (Tenders.Date >= ''' + convert(varchar(8),@BEGIN,112) + ''') 
		AND (Tenders.Date < DATEADD(dd, 1,'''+convert(varchar(8),@End,112)+'''))
		AND (Locations.[Location Name] = '''+@Location+''')
		AND (Tenders.[Location Name] <> N''Cash'')
            ) x
           PIVOT 
            (
               sum(Amount)   
               for MediaGroup in (' + @cols + ')
            ) p '

EXECUTE(@query)

END
RETURN
]

Open in new window

ste5anSenior DeveloperCommented:
Then you should check your data types in the tables and use an unambiguous date literal like '20150112' and '20151230'.

Your date literals depend on server, database and session settings.
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 2008

From novice to tech pro — start learning today.