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

16 comments:

Unknown 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

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

Unknown 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

Unknown 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

yanmaneee said...

curry 6 shoes
yeezy
nike epic react
goyard
pandora jewelry
golden goose sneakers
off white outlet
hermes
yeezy shoes
yeezy shoes