Archive
Comma separated values in mysql
MY SQL:
GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.
Syntax | GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’]) |
Quick Example | SELECT GROUP_CONCAT(city) FROM cities GROUP BY state; |
Separator | Comma (,) by default, ” eliminates separator |
NULL Values | Skipped |
Max Length | 1024 by default, specified by group_concat_max_len system variable |
Sample:
CREATE TABLE cities ( city VARCHAR(70), state CHAR(2) ); INSERT INTO cities VALUES ('San Francisco', 'CA'); INSERT INTO cities VALUES ('San Diego', 'CA'); INSERT INTO cities VALUES ('Los Angeles', 'CA'); INSERT INTO cities VALUES ('Austin', 'TX'); INSERT INTO cities VALUES ('Houston', 'TX');
SELECT state, GROUP_CONCAT(city) FROM cities GROUP BY state; The result:
CA | San Francisco,San Diego,Los Angeles |
TX | Austin,Houston |
SQL Server:
SELECT state, STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'') FROM cities c GROUP BY state Hope this help ! Rahul Bhatia
Visual Studio – Database Project Not Loading
Have you ever created a Database project and after some time you are not able to load it in the Visual Studio? Probably you might be getting following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
The database project requires an active database connection to load at design time. Probably it uses that to compile/verify scripts. The problem here is Visual Studio is not able to connect to any such instance. Following are few solutions (these worked for me)
- Make sure the instance to which you want to connect is running and allows remote connections.
- After performing step 1, if it is still not loading then you may need to start the SQL Browser Service. Make sure it’s running.
- If the problem is still there, it has to be fixed from Visual Studio itself. Actually database project do not store to which instance it can connect. It’s a Visual Studio setting.In VS2008 go to Tool > Options > Database Tools > Design-time Validation Database > Connection Options. Specify your instance name here like “\SQLEXPRESS”
Also If you upgraded your database project from 2005 to 2008 and if you try to open the 2008 database project solution file then you may get the following error:
Coluld not load file or assembly ‘System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken = 89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
To resolve this you need to install SQL Server CE 3.5 SP1 from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en
Hope it helps !
Cannot drop database because it is currently in use.
This is a very common error when DROP Database is command is executed and the database is not dropped.
The following commands will raise above error:
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO
Solution:
The following commands will not raise an error and successfully drop the database:
USE Master;
GO
DROP DATABASE AdventureWorks;
GO
If you are still getting the error after you try using
use master
go
drop database (databaseName)
go
Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
use master
go
drop database (databaseName)
go
Hope it helps!
COALESCE with DISTINCT in SQLServer
If you want comma separated values from columns it is so simple. Use COALESCE function provided by SQL Server.
DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ‘,’ , ‘ ‘) + cast(id AS VARCHAR) from tablename
print @csv
COALESCE with DISTINCT Values
If you want, DISTINCT values in COALESCE function use inner table like below.
DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ‘, ‘, ‘ ‘) + cast(id AS VARCHAR) from
(SELECT DISTINCT id FROM TableName)
print @csv
Hope it helps!
Comments