Thursday, June 25, 2009

Rename an Existing Column of a Table in MS SQL

In SQL server , Renaming a existing column without any data loss is possible .
A Inbuild System Stored Proc is available for the same

Syntax
EXEC sp_rename `tableName.[existingColumnName]`, `newColumnName`, `COLUMN`

Eg:
EXEC sp_rename `tblShortName.[longName]`, `shortName`, `COLUMN`

The above SP on execution will change the column longName in the tblShortName table to shortName

Note :

Take care you make the changes in all the stored proc and function where you are refering the old Column to the new Column

Tuesday, June 9, 2009

Convert Multiple Row results into a variable , comma seperated into a single Row result

I wanted to display a set of row results in a single Row or a single variable by comma seperated values in a SQL query.

DATA from a select statement is

NAME
-----

Alpha
Beta
Gaama

Need result in the manner of

Desired Result

Alpha,Beta,Gaama


The can be done by

DECLARE @strText varchar(Max)
SELECT @strText = COALESCE(@strText + ',', '') + CONTACT_NAME FROM table
SELECT @strText