troubleshooting Question

Problem Using PIVOT for Dynamic Columns

Avatar of rcowen00
rcowen00Flag for United States of America asked on
Microsoft SQL Server 2008Databases
8 Comments2 Solutions118 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros