Pivot BookSales

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

if OBJECT_ID('tempdb..#BookSales') is not null
   drop table #BookSales

CREATE TABLE #BookSales
(BookType VARCHAR(16), WeekDay Varchar(16), BookSales INT);
GO
 
INSERT INTO #BookSales VALUES('Route 1', 'Monday', 11201);
INSERT INTO #BookSales VALUES('Route 1', 'Sunday', 12939);
INSERT INTO #BookSales VALUES('Route 1', 'Sunday', 10436);
INSERT INTO #BookSales VALUES('Route 1', 'Tuesday', 9346);
INSERT INTO #BookSales VALUES('Route 1', 'Monday Thursday', 8956);
INSERT INTO #BookSales VALUES('Route 1', 'Friday', 7809);
INSERT INTO #BookSales VALUES('Route 2', 'Monday', 7214);
INSERT INTO #BookSales VALUES('Route 2', 'Friday', 5800);
INSERT INTO #BookSales VALUES('Route 3', 'Friday', 8922);
INSERT INTO #BookSales VALUES('Route 4', 'Wednesday', 7462);
INSERT INTO #BookSales VALUES('Route 6', 'Monday', 7739);
INSERT INTO #BookSales VALUES('Route 6', 'Tuesday', 6842);

--
;with #tempBook(BookType, WeekDay, BookSales) as (
select BookType, LEFT(WeekDay, CHARINDEX(' ',WeekDay+',')-1),
    STUFF(WeekDay, 1, CHARINDEX(' ',WeekDay+','), '')
from #BookSales
union all
select BookType, LEFT(WeekDay, CHARINDEX(' ',WeekDay+',')-1),
    STUFF(WeekDay, 1, CHARINDEX(' ',WeekDay+','), '')
from #tempBook
where WeekDay > ''
)
select BookType, WeekDay, BookSales
from #BookSales
order by BookType
--

DECLARE @sql AS NVARCHAR(2000);DECLARE @col AS NVARCHAR(2000);
 
SELECT @col = ISNULL(@col + ', ', '') + QUOTENAME(WeekDay)
FROM (SELECT DISTINCT WeekDay FROM #BookSales) AS BookTypes;
 
SET @sql =
  N'SELECT BookType, ' + @col +
   'FROM #BookSales
    PIVOT(SUM(BookSales)
    FOR WeekDay IN (' + @col + ')) AS PivotSales';
 
EXEC sp_executesql @sql;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results