asked on

Can PIVOT do what I want

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

rcowen00

ASKER

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

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rcowen00

ASKER

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)