title: Split the categorical column into multiple columns in SQL
body: <p>I have a toy data set generated by the following code:</p>
  
  <p><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)</code></p>
  
  <p><a href="https://i.stack.imgur.com/7J4wl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7J4wl.png" alt="enter image description here"></a></p>
  
  <p>I want to split on the column <code>property</code> into columns <code>property_VA</code> and <code>property_PZ</code>, which looks like the following:</p>
  
  <p><a href="https://i.stack.imgur.com/b6pCZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b6pCZ.png" alt="enter image description here"></a> </p>
  
  <p>Another question is that is that How can I append a new row called <code>total</code> to only get the sum of <code>LOS_VA</code> and <code>LOS_PZ</code>. I am not sure SQL can do this stuff</p>