Microsoft Definition:
This function returns first nonnull expresion among its argument.
You can use this function to get column values in one record set with separator like comma, dash etc. Before invention of this function, people used to achieve this kind of stuff by cursor which is time consuming. So, lets have a look at magical function by Microsoft.
Create following table in your database.
create table Testing( mid int ,name varchar(10) ,[18QI] varchar(10))
After creating the table, insert following records.
insert into Testing VALUES(1,'BC','01')
insert into Testing VALUES(2,'BC','10')
insert into Testing VALUES(3,'BC','02')
insert into Testing VALUES(4,'BC','04')
Now, we are all set to see the new powerful function COALESCE() in SQL-Server. Too see the magic, create following function in your database.
CREATE FUNCTION DBO.Get18QI(@ID char(100))RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @QIList varchar(1000)
SELECT @QIList = COALESCE(@QIList + ', ', '') + convert(varchar,T.[18QI]) FROM Testing T WHERE T.name = @ID and T.[18QI] is not null
RETURN @QIList
END
after successfully creating the function, here we go to see the cool result by following query.
select DBO.Get18QI('BC') as ComaValues
Hope you people will find this article useful.
Happy Working!!!!
COALESCE() function in SQL-Server for getting comma seperated value
Thursday, May 1, 2008 |
Posted by
Ritesh Shah
Subscribe to:
Post Comments (Atom)
Latest Article in Extreme-Advice
Ask Me SQL Server question
Recent comment here
Comments for Ritesh's Blog for SQL-SERVER & .NET
Pages
About Me
- Ritesh Shah
- Ritesh Shah is a data professional with having 10+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning. Apart from SQLHub, I used to share my knowledge at following places: http://blog.extreme-advice.com/ http://learn.extreme-advice.com/
Short Tips on Extreme-Exchange
Blog Roll
-
-
Black Friday Super Sale!5 weeks ago
-
-
Data Quake5 years ago
-
test wrong solution7 years ago
-
Updateable columnstore index gotchas11 years ago
Visitors
Categories
T-sql
(188)
stored procedure
(16)
index
(15)
SSIS
(13)
USER DEFINE FUNCTION
(9)
c#
(9)
audit trail
(7)
encryption
(7)
trigger
(7)
asp.net
(6)
BCP
(5)
CREATE TRIGGER
(5)
Full Text Search
(5)
OUTPUT
(5)
UDF
(5)
decryption
(5)
experts-exchange.com
(5)
information_schema.columns
(5)
sp_MSforeachtable
(5)
ALTER TRIGGER
(4)
cte
(4)
cursor
(4)
linked server
(4)
xml
(4)
CROSS APPLY
(3)
Contains
(3)
DMK
(3)
Inner Join
(3)
PIVOT
(3)
XP_CMDSHELL
(3)
aggregate function
(3)
bcp.exe
(3)
bulk insert
(3)
coalesce
(3)
dateadd
(3)
dynamic pivot
(3)
error fix
(3)
identity
(3)
instead of trigger
(3)
subquery
(3)
t-sq
(3)
teched event in Ahmedabad
(3)
view
(3)
Asymmetric
(2)
DELETED
(2)
FreeText
(2)
HTTP Endpoint
(2)
INFORMATION_SCHEMA.tables
(2)
INSERTED
(2)
Microsoft.ACE.OLEDB.12.0
(2)
OPENROWSET
(2)
OUTER APPLY
(2)
OUTPUT Parameter
(2)
PSEUDO TABLE
(2)
RANK()
(2)
Read Committed Isolation Level
(2)
Read UnCommitted Isolation Level
(2)
SMK
(2)
SNAPSHOT ISOLATION
(2)
SPLIT
(2)
Serializable Isolation Level
(2)
Symmetric key
(2)
Sys.Objects
(2)
T SQL puzzle
(2)
TCP Endpoint
(2)
Visual Studio 2008
(2)
WHERE Condition
(2)
bulk copy
(2)
catalog view
(2)
cdc
(2)
certificate in database
(2)
change data capture
(2)
charindex
(2)
collation
(2)
count
(2)
create login
(2)
create master key
(2)
cube
(2)
database master key
(2)
datediff
(2)
delete
(2)
dmv
(2)
example of Silverlight
(2)
fast_forward
(2)
find word in stored procedure
(2)
for xml path
(2)
insert into
(2)
isnull
(2)
join
(2)
load XML into SQL
(2)
login
(2)
pivot with where condition
(2)
service master key
(2)
sp_MSforeachdb
(2)
sp_configure
(2)
sp_executeSQL
(2)
sp_spaceused
(2)
split value
(2)
sql server agent jobs
(2)
sum
(2)
sys.schemas
(2)
teched
(2)
teched on road
(2)
update puzzle
(2)
user
(2)
vb.net
(2)
what is identity
(2)
.DTS
(1)
.DTSX
(1)
.NET
(1)
.NET 3.5
(1)
.NET framework 4.0
(1)
ACCESS
(1)
ALTER PROC
(1)
ALTER SCHEMA
(1)
ASCII to CHARACTER
(1)
Argument data type ntext is invalid
(1)
BLOB
(1)
BSA
(1)
Bug Fix
(1)
CHAR
(1)
CHECK
(1)
CLR function
(1)
CODEPLEX Toolkit
(1)
COMPUTE
(1)
COPY ONE TABLE FROM ANOTHER
(1)
CREATE PROC
(1)
CREATE RULE
(1)
CREATE SCHEMA
(1)
Cannot generate SSPI context
(1)
Cascading Delete
(1)
Channel 9
(1)
ContainsTable
(1)
Cross Tab Query
(1)
DBCC INDEXDEFRAG
(1)
DBCC SHOWCONTIG
(1)
DDL TRIGGER
(1)
DML trigger
(1)
DRI
(1)
DROP PROC
(1)
DROP SCHEMA
(1)
DTS
(1)
DataFlow
(1)
Database Engine tuning adviser
(1)
DatabaseMailUserRole
(1)
Definition OF SCHEMA
(1)
ENABLE XP_CMDSHELL
(1)
EVENTDATA()
(1)
EXEC sp_BindRule
(1)
HAVING Clause
(1)
IMPROVED INSERT
(1)
INSERT with Raw Constructor
(1)
Installation of Silverlight
(1)
JOB List
(1)
KEEPFIXED PLAN
(1)
LOG
(1)
MSforEachDB
(1)
Microsoft sync service
(1)
Microsoft.Jet.OLEDB.4.0
(1)
OUTER join
(1)
OVER
(1)
Partition by
(1)
Right Join
(1)
SELECT INTO
(1)
SELECT UNION ALL
(1)
SERVERPROPERTY
(1)
SET
(1)
SQL Injection
(1)
SQL Server profiler
(1)
Session Variable
(1)
Sys.Columns
(1)
Sys.Database_files
(1)
Sys.Databases
(1)
Sys.Foreign_Key_Columns
(1)
Sys.Key_Constraint
(1)
System.IO.DriveInfo
(1)
System.IO.DriveInfo.GetDrives()
(1)
TOP
(1)
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered
(1)
UTC datetime
(1)
Unable to start debugging on web server. Debugging failed because integrated windows authentication is not enabled. Please see help for assistance
(1)
Uniqueidentifier
(1)
Update table
(1)
View should be with schemabindings
(1)
Visual Studio 2010
(1)
Windows Installer Cleanup Utility
(1)
YUKON
(1)
access 2007 in SQL Server
(1)
addrolemember
(1)
ado.net
(1)
alter all column
(1)
archive
(1)
archive data in SQLServer
(1)
ascii
(1)
backup master key
(1)
body surface area
(1)
bulk operation
(1)
c-sharp
(1)
calculate clustered index size
(1)
calculate index size
(1)
calculate non-clustered index size
(1)
cannot open database
(1)
cascading Update
(1)
cerate certificate
(1)
change collation
(1)
change column name
(1)
change database mode
(1)
character count
(1)
check database size
(1)
check file exists in SQL Server
(1)
client/server application
(1)
comma separated value
(1)
comparison of SP and UDF
(1)
conditional split transformation
(1)
configure mySQL in windows
(1)
configure php
(1)
configure php in IIS
(1)
convert local datetime to UTC
(1)
copy backup from one location to another
(1)
copy files
(1)
copy files and folder from SQL Server
(1)
copy ldf
(1)
copy mdf
(1)
correlated subquery
(1)
create CSV file from SQL
(1)
create clustered index on view
(1)
create database master key
(1)
create nonclustered index on view
(1)
create synonym
(1)
create txt file
(1)
create user
(1)
create view
(1)
data abstract layer
(1)
data access error
(1)
data file size
(1)
data management view
(1)
data size in table
(1)
database trigger
(1)
date
(1)
datetime bug
(1)
datetime format
(1)
day calculate with CTE
(1)
db_name()
(1)
definition of cursor
(1)
delete all records
(1)
delete in bunch
(1)
delete many records
(1)
delete multiple records
(1)
delete with JOIN
(1)
dynamic word document with image with C#
(1)
email
(1)
email from trigger
(1)
email when run out of space
(1)
environtment.currentdirectory
(1)
error 7303
(1)
error 7411
(1)
error 8152
(1)
except
(1)
exclusive lock
(1)
export data from SQL to CSV
(1)
export data from SQL to excel
(1)
export from SQL to Flat file
(1)
find GMT +5.30 time
(1)
find columns
(1)
find data file size
(1)
find default value of column
(1)
find disk space usage
(1)
find error log file
(1)
find first Friday
(1)
find foreign key
(1)
find lock
(1)
find locked table
(1)
find log file size
(1)
find primary key
(1)
find stored procedure
(1)
find table list
(1)
find user
(1)
find user in all database
(1)
find user permission
(1)
free SQL Server 2008 ebook
(1)
fuzzy lookup
(1)
get drive infor of windows
(1)
grantdbaccess
(1)
grantlogin
(1)
grouping sets
(1)
grouping sets in SQL Server 2008
(1)
image store in sql server
(1)
import excel file to sql server
(1)
inline table valued function
(1)
insert image in sql server
(1)
insert trigger
(1)
insert varbinary
(1)
instance
(1)
internet explorer
(1)
intersect
(1)
keep eye on database file
(1)
keep eye on hard disk space
(1)
keep track of database file
(1)
master.dbo.xp_fileexist
(1)
msdb..sysjobs
(1)
msdb.dbo.sp_send_dbmail
(1)
padindex
(1)
proper case
(1)
recursive query
(1)
regex.split
(1)
regional language in sql server
(1)
restore master key
(1)
return value from SP
(1)
revoke access of user
(1)
server trigger
(1)
servername
(1)
set database offline
(1)
set database online
(1)
set default in all column
(1)
set identity_insert
(1)
shrink log file
(1)
shrink log file of all database
(1)
single_user
(1)
sp_addrolemember
(1)
sp_fulltext_catalog
(1)
sp_fulltext_column
(1)
sp_fulltext_database
(1)
sp_fulltext_table
(1)
sp_procoption
(1)
sp_rename
(1)
sp_send_dbmail
(1)
split value in c#
(1)
sql
(1)
sql server energy event
(1)
sql server instance
(1)
startup stored procedure
(1)
static cursor
(1)
store document in sql server
(1)
string or binary data would be truncated
(1)
stuff
(1)
sub cte
(1)
subquery as table
(1)
substring function
(1)
synonym
(1)
sys.database_principals
(1)
sys.database_role_members
(1)
sys.procedures
(1)
syscomments
(1)
sysindexes
(1)
sysjobhistory
(1)
sysobject
(1)
sysobject.xtype
(1)
system.serviceprocess
(1)
table naming convention
(1)
table statistics
(1)
title case
(1)
total
(1)
total row in table
(1)
truncate
(1)
txt file
(1)
union
(1)
union all
(1)
union in CTE
(1)
update
(1)
update XML column
(1)
update XML data
(1)
update fun
(1)
update image in sql server
(1)
update null data
(1)
update null for all column
(1)
update statestics
(1)
update with aggregate function
(1)
update()
(1)
use of cube and rollup
(1)
user account in SQL Server
(1)
user and login issue
(1)
user defined rules
(1)
watch your hard disk space
(1)
web.config
(1)
wedding anniversary
(1)
where
(1)
windows appilcation
(1)
windows server 2003
(1)
xlsx in sql server 2005
(1)
xml relational data in sql table
(1)
xp_fileexist
(1)
xp_fixeddrives
(1)
xp_sendmail
(1)
xtype
(1)
– Context_Info
(1)
1 comments:
Do comment about your experience about his article. It will help me to cater you better.
Post a Comment