Restoring the MSSQL Database

Restoring of the database can be done through the database menu, or [I prefer] like that.

RESTORE DATABASE DatabaseName
FROM DISK = 'C:\Path\To\Backup\File\DatabaseName.bak'
WITH REPLACE

If you get the following error: "Exclusive access could not be obtained because the database is in use", try to get the list of connected users:

EXEC SP_WHO2

Find ones that are connected to DatabaseName and kill them

KILL [SPID]

Date (Without Time) In MsSql

Should be something like that:

SELECT dateadd(day, datediff(day, 0, getdate()), 0)

Concatenate Strings On Group By In Sql

Imagine we have the following table.

CREATE TABLE MyTable (id int, name varchar, value int);

INSERT INTO MyTable (id,name,value) VALUES (1, 'Hello', 4);
INSERT INTO MyTable (id,name,value) VALUES (1, 'World', 8);
INSERT INTO MyTable (id,name,value) VALUES (5, 'Great!', 9);

The result we would like to acquire is:

| id |   name_values    |
+----+------------------+
|  1 | Hello:4; World:8 |
|  5 | Great!:9         |

Names and values are concatenated into strings and grouped by id. We need an aggregate function, that concatenates strings for that. Here are some solutions for different sql databases.

MySql

This case is most easy one. Lucky users already have the GROUP_CONCAT(expr) function. This query should give the answer.

SELECT id, GROUP_CONCAT(name + ':' + value SEPARATOR '; ') AS name_values FROM MyTable GROUP BY id;

PostgreSql

The solution here is a bit more difficult, but nevertheless easy enough. We need to create our own aggregate function and use it in our query.

CREATE AGGREGATE aggr_textcat(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT id, substring(aggr_textcat(', ' || name || ':' || value) from 2) AS name_values FROM MyTable GROUP BY id;

Here we used already existing function to concatenate text fields textcat, but we could write our own.

MsSql

Since version 2005 it became also possible to write your own aggregate function in MsSql, but here I provide another solution using inner select and xml path.

SELECT id, SUBSTRING((SELECT '; ' + name + ':' + CAST(value AS varchar(MAX)) 
FROM MyTable WHERE (id = Results.id) FOR XML PATH ('')),3,9999) AS name_values
FROM MyTable Results
GROUP BY id