Tuesday, 3 February 2009

How to combine Multiple Rows into Single Column in SQL Server

I have come across a link where you can combine multiple rows into single column using the COALESCE function.It is very simple as shown below

CREATE TABLE WeekDays
([Name] varchar(40))
INSERT INTO WeekDays VALUES('Mon');
INSERT INTO WeekDays VALUES('Tue');
INSERT INTO WeekDays VALUES('Wed');

DECLARE @str VARCHAR(2000)
select @str = COALESCE(@str + ',', '') + [Name]
from WeekDays
SELECT @str


Output :Mon,Tue,Wed

You can see the original article on
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

21 comments:

Nitesh said...

I've used CTE

create table t1
(
val varchar(100),
)


insert into t1 values ('A')
insert into t1 values ('B')
insert into t1 values ('C')
insert into t1 values ('D')
insert into t1 values ('E')


with CTE (id,val)
AS
(
select id,val from (select Row_number() over (order by val) as id, val from t1) e where id = 1
union all
select e1.id, CAST ( CTE.val + ',' + e1.val As varchar(100)) As val from CTE
inner join (select Row_number() over (order by val) as id, val from t1 ) e1 on e1.id = CTE.id + 1
)
select top 1 val from CTE order by id desc

html5 media player said...

I just searching this kind of things in search engines. My searching was ending here. Keep up your good work. I bookmarked it for general updates.
youtube html5 player

Anonymous said...

YES, its work thank u..

Anonymous said...

It is in point of fact a great and helpful piece of information.
I'm satisfied that you simply shared this helpful info with us. Please keep us informed like this. Thank you for sharing.

Take a look at my blog - the motorcycle

Anonymous said...

Write more, thats all I have to say. Literally, it seems as though you relied on the video
to make your point. You clearly know what youre talking about, why waste your intelligence on just
posting videos to your weblog when you could
be giving us something enlightening to read?


my weblog; see more

Anonymous said...

I visited multiple web sites except the audio quality
for audio songs current at this site is genuinely wonderful.


Here is my blog post: clear

Anonymous said...

Thanks for your publication on the traveling industry.
I might also like contribute that if you're a senior thinking about traveling, its absolutely vital that you buy travel cover for elderly people. When traveling, older persons are at greatest risk of getting a healthcare emergency. Obtaining the right insurance coverage package on your age group can protect your health and provide peace of mind.

Feel free to visit my page :: http://www.internetdershanem.com/

Anonymous said...

Hi there, I discovered your site by way of Google whilst searching for a related topic, your web site got here up,
it looks good. I've bookmarked it in my google bookmarks.

Here is my web blog - canadian dating

Anonymous said...



Also visit my web page - facebook for sex

Anonymous said...

Great post. I was checking constantly this blog and I am impressed!

Extremely helpful information particularly the last part
:) I care for such info much. I was looking
for this particular information for a very long time. Thank you
and good luck.

Feel free to surf to my web site :: google dating

Anonymous said...

As I web-site possessor I believe the content material here is rattling fantastic , appreciate it
for your efforts. You should keep it up forever! Best of luck.


Feel free to surf to my website :: dating sites and personals

Anonymous said...

This design is incredible! You obviously know how
to keep a reader amused. Between your wit and
your videos, I was almost moved to start my own blog (well, almost.
..HaHa!) Great job. I really loved what you had
to say, and more than that, how you presented it.
Too cool!

My web site click through the following Web Site

Anonymous said...

Thanks for the diverse tips contributed on this weblog.
I have seen that many insurers offer consumers generous discount rates if they prefer to insure several
cars together. A significant amount of households include several motor vehicles these days, particularly people with mature teenage kids still
residing at home, as well as savings on policies might soon
begin. So it pays off to look for a great deal.

Check out my web blog - adult personals

Anonymous said...

I'm impressed, I must say. Rarely do I encounter a blog that's both equally
educative and engaging, and without a doubt, you have hit the nail
on the head. The issue is something that too few people
are speaking intelligently about. I'm very happy I found this in my search for something concerning this.

Feel free to visit my weblog: a porn Tube

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

oakleyses said...

north face, sac michael kors, abercrombie and fitch, scarpe hogan, nike air max, hollister, louis vuitton, nike free, true religion outlet, ray ban uk, vans pas cher, oakley pas cher, ray ban pas cher, lululemon, hollister, air max pas cher, michael kors canada, north face pas cher, air jordan, longchamp pas cher, nike trainers, nike free pas cher, hermes pas cher, air max, sac louis vuitton, true religion outlet, guess pas cher, converse pas cher, true religion, mulberry, nike huarache, timberland, ralph lauren pas cher, burberry pas cher, nike air force, sac vanessa bruno, lacoste pas cher, louis vuitton uk, nike roshe run, chaussure louboutin, longchamp, ralph lauren, barbour, nike roshe, nike blazer pas cher, michael kors uk, tn pas cher, new balance pas cher, louis vuitton pas cher, nike air max

oakleyses said...

giuseppe zanotti, insanity workout, canada goose, valentino shoes, bottega veneta, new balance outlet, canada goose pas cher, marc jacobs outlet, moncler, asics shoes, beats headphones, hollister, reebok shoes, ugg boots, canada goose outlet, vans outlet, rolex watches, ugg outlet, moncler outlet, canada goose outlet, mac cosmetics, lululemon outlet, uggs outlet, wedding dresses, ferragamo shoes, soccer shoes, babyliss pro, north face outlet, mont blanc pens, jimmy choo shoes, mcm handbags, longchamp, ugg, canada goose uk, soccer jerseys, ghd, birkin bag, uggs on sale, celine handbags, moncler, nfl jerseys, roshe run, chi flat iron, instyler ionic styler, herve leger, p90x workout, ugg soldes, abercrombie and fitch, north face jackets, canada goose outlet

ninest123 said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

ninest123 said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03