Split the categorical column into multiple columns in SQL

I have a toy data set generated by the following code:

declare @tbl table (year int, month int, property varchar(4), nights varchar(4), LOS int) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'VA', '1', 33) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'VA', '2', 43) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'VA', '3', 51) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'VA', '4', 27) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'VA', '5+', 82) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'PZ', '1', 37) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'PZ', '2', 63) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'PZ', '3', 41) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'PZ', '4', 67) insert into @tbl (year, month, property, nights, LOS) VALUES (2017, 1, 'PZ', '5+', 52)

enter image description here

I want to split on the column property into columns property_VA and property_PZ, which looks like the following:

enter image description here

Another question is that is that How can I append a new row called total to only get the sum of LOS_VA and LOS_PZ. I am not sure SQL can do this stuff

1 answer

  • answered 2018-01-11 20:51 Von Aaron

    One option is to use PIVOT, example

        SELECT  year ,
                month ,
                nights ,
                'VA' Property_VA ,
                VA LOS_VA,
                'PZ' Property_PZ ,
                PZ LOS_PZ
        FROM    ( SELECT    *
                  FROM      @tbl
                ) TBL PIVOT ( SUM(LOS) FOR property IN ( VA, PZ ) ) PVT
    

    Result

        year        month       nights Property_VA LOS_VA          Property_PZ LOS_PZ
        ----------- ----------- ------ ----------- ----------- ----------- -----------
        2017        1           1      VA          33              PZ          37
        2017        1           2      VA          43              PZ          63
        2017        1           3      VA          51              PZ          41
        2017        1           4      VA          27              PZ          67
        2017        1           5+     VA          82              PZ          52
    
        (5 row(s) affected)