CREATE table #sourceTable (Id int, Dates DateTime, FoodNa...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Writing Meta

Q&A about the site for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

CREATE table #sourceTable
    (Id int, Dates DateTime, FoodName varchar(13),
    AmountEaten int);
    
INSERT INTO #sourceTable
    (Id, Dates, FoodName, AmountEaten)
VALUES
    (1, '2019-08-01', 'Sammich', 2),
    (2, '2019-08-01', 'Pickle', 3),
    (3, '2019-08-01', 'Apple', 1),
    (4, '2019-08-02', 'Sammich', 1),
    (5, '2019-08-02', 'Pickle', 1),
    (6, '2019-08-02', 'Apple', 4),
    (7, '2019-08-03', 'Cake', 2),
    (8, '2019-08-04', 'Sammich', 1),
    (9, '2019-08-04', 'Pickle', 2),
    (10, '2019-08-04', 'Apple', 3)
;

SELECT Dates AS 'Day',
Sammich, Pickle, Apple, Cake
FROM (
    SELECT Dates, FoodName, AmountEaten FROM #sourceTable
) AS SourceTable
PIVOT (
    MAX(AmountEaten)
    FOR FoodName IN (Sammich, Pickle, Apple, Cake)
) AS PivotTable

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results