How to concatenate data from multiple rows into a single rows

I had a problem once. One table had a lot of data in a column on multiple rows. I had to display it in a view but I needed all the data on that column to be displayed in a single cell on a row. This view was supposed to be the data source for a report ... long story ...

Anyway, the basic idea is this:

SELECT some_column = (your select query
 for xml path(''))

this only works in sql server 2008 because earlier versions don't have the "xml" keyword.

Here is an example:

SELECT Documente = (SELECT coalesce(td.Descriere+',','') as [text()] FROM Document d 
   INNER JOIN TipDocument td ON d.TipDocumentId = td.Id  for xml path(''))

Opening compressed databases or .mdf files in SQL Server Management Studio

If when you try to open a database or attaching a .mdf file you get the error:

is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

So here is the algorithm I followed to solve this issue:

1. only if an existing database gives this error detach the database – right click -> Detach

2. closed all SQL Server Management Studio instances

3. Stop the SQL Server service.. (Start->Run -> services.msc)

4. Selected .mdf file(should be blue if compressed ) Right click properties -> Advanced -> Uncheck "Compress files to save disk space"

5.(re)attach .mdf file

Setting the week's start day in MS SQL

SET DATEFIRST 1 --start day is Monday

SET DATEFIRST 7 --start day is Sunday – default

To test it PRINT @@DATEFIRST