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), NewColumn varchar(13),
    AmountEaten int);
    
INSERT INTO #sourceTable
    (Id, Dates, FoodName, AmountEaten, NewColumn)
VALUES
    (1, '2019-08-01', 'Sammich', 2, 'test1'),
    (1, '2019-08-01', 'Pickle', 2, 'test1'),
    (2, '2019-08-01', 'Pickle', 3, 'test2'),
    (3, '2019-08-01', 'Apple', 1, 'test3'),
    (4, '2019-08-02', 'Sammich', 1, 'test4'),
    (5, '2019-08-02', 'Pickle', 1, 'test5'),
    (6, '2019-08-02', 'Apple', 4, 'test6'),
    (7, '2019-08-03', 'Cake', 2, 'test7'),
    (8, '2019-08-04', 'Sammich', 1, 'test8'),
    (9, '2019-08-04', 'Pickle', 2, 'test8'),
    (10, '2019-08-04', 'Apple', 3, 'test8')
;

SELECT 
    Dates AS 'Day',
    NewColumn AS 'Test',
    ISNULL(Sammich, 0) AS Sammich,
    ISNULL(Pickle,  0) AS Pickle, 
    ISNULL(Apple,   0) AS Apple,
    ISNULL(Cake,    0) AS Cake
FROM (
    SELECT Dates, FoodName, AmountEaten, NewColumn 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