"AC" SQL 2005 overflow: Merging column values

Problem Description:

There is no aggregate function for strings in SQL 2000 or in SQL 2005, so it's a hassle when we're dealing with the following requirements:

There are table TB, as follows:

ID value

----- ------

1 AA

1 BB

2 AAA

2 BBB

2 CCC

Need to get results:

ID values

------ -----------

1 AA,BB

2 AAA,BBB,CCC

That is, the group by ID, the sum of value (string addition)

1. The old solution

--1. Creating a handler function

CREATE FUNCTION dbo.f_str (@id int)

RETURNS varchar (8000)

As

BEGIN

DECLARE @r varchar (8000)

SET @r = '

SELECT @r = @r + ', ' + value

From TB

WHERE id= @id

Return STUFF (@r, 1, 1, ' ")

End

Go

--Calling a function

SELECt ID, values=dbo.f_str (ID)

From TB

GROUP by ID

--2. New ways to solve the problem

--Sample data

DECLARE @t TABLE (id int, value varchar (10))

INSERT @t SELECT 1, ' AA '

UNION all SELECT 1, ' BB '

UNION all SELECT 2, ' AAA '

UNION all SELECT 2, ' BBB '

UNION all SELECT 2, ' CCC '

--Query processing

SELECT *

From (

SELECT DISTINCT

Id

From @t

) A

OUTER APPLY (

SELECT

[Values]= STUFF (replace (

(

SELECT value from @t N

WHERE id = a.id

For XML AUTO

), ' <n value= ', ', ', ', '/> ', ', 1, 1, '

) N

/*--Results

ID values

----------- ----------------

1 AA,BB

2 AAA,BBB,CCC

(2 rows affected)

--*/

CSDN Community Post Address

Attached: The CLR for merging and splitting, sql2005 examples include:
After installing the example of SQL 2005, the default installation directory is drive:\program Files\Microsoft SQL Server\90\samples\engine\programmability\clr\ In StringUtilities

trackback:http://tb.blog.csdn.net/trackback.aspx?postid=784287

[Click here to collect this article] posted on June 09, 2006 20:31:00

KXCC published in 2006-06-10 13:56:00 ip:221.6.3.*
Earth-shattering posts

Read the Landlord's post, can not help but the spirit of spirits, consciously seven through eight veins for one Chang, Qiqiao pour also opened six Qiao half, hero out of childhood, the landlord is young, there are ability, the wisdom of the national security state, the Ancients Cloud, lying Dragon and Phoenix Young One and Ann World, now, God bless my Greater China, the Earth, 5000, Chinese Shenzhou suddenly appearing, Feishazoushi, big crowd wild days, hazy, saw indomitable spirit of God in heaven and earth, this man is armed with double axes, two eyes such as electricity, an axe down, chaos at the beginning, two axe down, Nu wa made people, three ax down, niche dumping. Get this big hero, the good luck also, the people's blessing also, how does not call the human joy extremely to cry. The ancients have young landlord said as proof, the young landlord such as the Red Sun Rising, its road big light, river Chivu flow, a purge the ocean; submerged dragon, 鳞爪 fly, Milk Tigers Valley, beasts earthquake, eagle Test wings, dust suction Zhang, odd flowers first, make extracts, gan fa Xing, there for its awn, days wear its pale, the ground is yellow, there are ages, horizontal Bala , a niche to the landlord of admiration such as surging river, lasting, the sky, never change.

After reading the landlord's post, my mood was a long time can not calm. Like Lao Tze's cloud: The Big sound is Greek sound, the elephant is invisible. I now finally understand what I lack, it is the landlord that the persistent pursuit of truth and the landlord that the ideal of the hard practice produced by the heavy feeling. In the face of the landlord's post, I was so shocked that I could hardly move, the landlord kind of crack paper to out of the big, unexpectedly I can not help but to open the post of the landlord again and again, every time, appreciation of the feeling on the long number of points, I always think, whether there is a deity living in its scenery under the appearance, so that people do not know the meat taste, so that people have buzzing, 3rd the Feeling. Landlord, you write is very good. The only thing I can do is to put this post on the top of the story.

The landlord's post is really written too good. Fluent style, rhetorical, won the Wei and Jin Dynasty legacy, more will Tang Feng song bone to carry forward penetrating, can see the landlord in their lifetime this post. It's really my sansheng. After reading this post of the landlord, I unexpectedly produced a sense of grief--ah, such a good post, if I can not see the future, then what should I do? So what am I supposed to do? Until I did not hesitate to put the landlord of this post collection, the excitement in my heart gradually calmed down. But I immediately thought, such a good post, if others do not see, then is not a waste of the landlord's efforts? After a painful struggle, I finally made up my mind to sacrifice my ego and dedicate myself. I want to take out this post dedicate to the world tours, I want this post to go up top, up top! Until everyone sees it!

Before I met you, I doubted whether there was a real sage in the world, and now I believe it! I have been indifferent to the song Fu in the Han Dynasty, I have been surprised at the Li du poem, I have been lingering in song and Yuan lyrics. But now, I know how shallow I am!

The landlord, your noble sentiment too lets the person move. In the present such a materialistic money society, unexpectedly still can see the landlord such temperament, is undoubtedly my greatest fortune in this life. Let me deeply feel the greatness of human nature. The landlord's post, just like the Dark thorn in the night sky Lightning, but also like tearing the dark clouds of sunshine, the moment let me like drinking dew, let me understand the eternal truth in this world is real. Only the landlord so with a broad mind and a complete knowledge system, can be the only speaker of this truth. Read the Landlord's post, I fell into a serious thinking. I think that if the landlord's posts are not top up, is a betrayal of the truth, is a great compromise on the fallacy. Therefore, I decided to return to the top of righteousness!

Well said! I have been rolling around here for so many years, the so-called read countless people, even if you have not seen a pig walk, but also always understand what the taste of pork. A see the momentum of the landlord, I think the landlord and in the community irrigation of the small gangs have the essential difference! The melancholy tone, the familiar signature, and the farsighted of the lines. No use, landlord, even if you change a vest is useless, your millions of followers have already recognized you, you must be the legendary strongest ID. Since the community revision, I have been discouraged, the community has not held any hope, the legend has been disillusioned, the myth has ended, stay in the community what do you mean? Unexpectedly, did not expect, today can again see the style of the landlord, I was so excited to be in front of the screen shed tears. Yes, as long as the landlord under the leadership of the community there is hope. Once again my heart was boiling, and the blood in my chest was burning again. The landlord of a few words, although simple, but general summary, a word of our instead focusing for many years still can not solve the fundamental problems of several major issues. The landlord is like the beacon of the community, the landlord is like the direction of the community, the landlord is like the pillars of the community. There is a landlord in the community tomorrow will be better!

The master's words really like "big sound to Sweep Yin", like "Ching", so that I wait for netizens to see the hope, see the future! Thunderbolt, the enlightened perhaps not enough to describe the master article in case, Wushan line Cloud, the Yangtze River is more difficult to compare the Master's wisdom! Huang Zhongdalu, enlightening! You candle to the world, Ming Wanli, rain, ze by Wanfang! Through your deep words, I seem to see you eagle look Wolf gu, Long's stalwart heroic; as if to see your hands like rafters, write the wisdom of the World article, as if saw you by the sword Si Gu, round of the noble spirit!

Word after reading this post, my heart can not calm, shock ah! Why there is such a good post! I have been in the network forum for many years, I think I will never have any posts can impress me, did not expect to see today so exquisite such a post! Landlord, you let me deeply understand the "people outside someone, behind" this sentence. Thank you, Lennon! After reading this post, I did not immediately reply, because I am afraid that my vulgar and unbearable reply will tarnish this online rare posts. But I still replied, because I feel that if you can not in such a wonderful post after leaving their own network name, then I will not die of death! How proud to be able to leave your net name behind such a wonderful post! Landlord, please forgive me for being selfish! I know that no matter how gorgeous rhetoric to describe the wonderful degree of your posts is not enough, are hypocritical, so I just want to say: Your post is too good-looking! I would like to watch forever! This post idea novel, the subject originality, the paragraph clear, the plot is strange, the ups and downs, the main line is distinct, is fascinating, the plain shows the Uncommon Literature Foundation, is well-written, the sentence classics, is the model which our I should study. Is the so-called: "A horse Pentium, shootout bow, heaven and earth in my heart!" "The landlord really deserves to be a new generation of the mountain strange!" I was already disappointed in this community, I feel that the community has no future, the heart is full of sadness. But looking at your post gives me hope for the community. Is you let my heart rekindle the fire of hope, is you let my heart revive, is you saved me a cold pull out cool heart! Originally I decided not to return any posts in the community, but read your post, I told myself this post is sure to return! This is a rare in a century of good stickers ah! Heaven has eyes ah, let me in my lifetime to view such a wonderful and incomparable posts!

Cocoa published in 2006-06-11 00:10:00 ip:58.60.40.*
Oh, what a fart, upstairs brother you are not from the business is too bad, you should give up technology, immediately turn to do sales, in contrast to you in the technology of this piece of development is bigger.

Mijie published in 2006-06-11 09:34:00 ip:218.80.67.*
Good, good.

Sssssssssss published in 2006-06-11 19:34:00 ip:222.212.212.*
Cocoa Don't scold
KXCC that post is a curse.
It's a lot of stuff online.

Alphateam published in 2006-06-11 19:36:00 ip:222.212.212.*
The new method is not necessarily a good thing.
The old Oracle should be able to do the following
The new one is another Microsoft conspiracy.

Sqlhub published in 2006-06-12 20:18:00 ip:221.221.254.*
Ms Support is good, support is not difficult, hgsql support
Select ID, strsum (value, ', ')
From T
Group by ID;