Post reply

Reconfiguring Data

  • June 7, 2017 at 9:03 am

    #337541

    I have a database that goes like below.

    ID,     Value,     Value Type
    1,       23,         Location
    2,       34,         Location
    1,       44,         Vehicles
    2,       34,         Vehicles
    1,       57,         Bikes
    2,       42,         Bikes

    What I need is

    ID,     Location,    Vehicles,    Bikes
    1,      23,             44,             57
    2,      34,             34,             42

    I'm kind of lost on how to get here, probably pretty simple.

  • June 7, 2017 at 10:27 am

    #1945853


    DECLARE @tbl table(ID int, Val int, ValType varchar(20));
    INSERT @tbl (ID, Val, ValType)
    VALUES (1, 23, 'Location')
         , (2, 34, 'Location')    
         , (1, 44, 'Vehicles')
         , (2, 34, 'Vehicles')
         , (1, 57, 'Bikes')
         , (2, 42, 'Bikes');
    SELECT ID, [Location], [Vehicles], [Bikes]
    FROM @tbl t
    PIVOT
       (Sum(Val) FOR ValType IN ([Location], [Vehicles], [Bikes])
       ) pvt;

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply