Multi-column to 1-column SQL Server implementation oracle10g Wmsys.wm_concat ()--for XML Path (")

There is such a table

Create TableTB (IDint, valuevarchar(Ten))  Insert  intoTbValues(1,'AA')  Insert  intoTbValues(1,'BB')  Insert  intoTbValues(2,'AAA')  Insert  intoTbValues(2,'BBB')  Insert  intoTbValues(2,'CCC')  Insert  intoTbValues(3,'DDD')  Insert  intoTbValues(3,'FFF')

Requirement: The ID is 3 so the data is converted to 1 rows. Separated by |

1. Transfer the data with ID 3 to XML XML   Select ' | ' + (value) from TB where id=3 For XML Path (")Effect

The FOR XML path (') brackets add something. Words. tags similar to <li></li> 2. Get rid of the first | Replacing a space requires a function Stuff, similar to a function such as a string operation, and replace () right () to left () substring () to see the need to use

The STUFF function inserts a string into another string. It removes the specified length of characters from the starting position in the first string, and then inserts the second string at the beginning of the first string.

Transact-SQL Syntax conventions

Grammar

STUFF (,)

Stuff ((SELECT ' | ') + (value) from Tbwhere id=3for XML Path (")), 1, 1, ') This sentence cannot be run alone

3. Final realization

SelectID, value=Stuff((Select '|'+(value) fromTBwhereId=3 forXML Path ("')),1,1,"') fromTbwhereId=3 Group  byId

PS: To get rid of a where results are not the same, do not understand why. Group by ID has a de-weight effect

。 No------------

He also has his own implementation in the Oracle database. Think there's nothing about these things. As long as you have the demand, there must be an answer, but you touch it. Anyway, we're all standing on the shoulders of giants now.

Multi-column to 1-column SQL Server implementation oracle10g Wmsys.wm_concat ()--for XML Path (")