Thursday, 19 May 2011

Column values as a single string - MS Sql Server

There are situations where you need the resultant values of a single column to be displayed/processed as a single string. So here goes the query:

SELECT @Str = COALESCE(@Str+',' ,'') + CONVERT(varchar,Column1) FROM MyTable

Multiple Count from Single Table Sql Server

Recently my collegue was working with a query where he needed multiple counts from a table with different conditions. Say its something like a company has multiple office buildings with multiple floors. They need the count of total space for each floor of each building and empty space with the same conditions.
The query I referred was like this:

SELECT SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) count1, SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) count2 FROM yourtable

Customizing it to my need I wrote the following query

SELECT SUM(CASE SeatStatus WHEN 0 THEN 1 ELSE 0 END) FreeSpace, COUNT(Seat) TotalSpace FROM MyTable