Can PIVOT do what I want

rcowen00
rcowen00 used Ask the Experts™
on
I have a stored procedure that returns using PIVOT.  

Column1        Column2       Column3
178.09             19.02               124.45        

Can PIVOT return the following?
 Date          Column1        Column2       Column3
12/01          50.09              4.00               50.45  
12/02          78.00              1.02                24.00
12/03           50.00             15.00              50.00
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
ALTER PROCEDURE [dbo].[SalesJournal_NEW]
	@Location varchar(25)='147',
	@Begin smalldatetime ='12/1/15' ,
	@End smalldatetime ='12/30/15',
	@UserId varchar (75)='ddd'
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

Technical Architect, Capgemini India
Commented:
In the SELECT query that supplies data to the PIVOT operator, also fetch the required date and I believe you should be all set.

Author

Commented:
Added the date column to this section and it worked great.  Thanks!


SET @query = 'SELECT * from  
             (
                SELECT m.MediaGroup, Tenders.Amount AS Amount,Tenders.Date  
            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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial