---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-05-05 15:41:39 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[test] if object_id('[test]') is not null drop table [test] go create table [test]([id] int,[name] nvarchar(2)) insert [test] select 1,'w' union all select 2,'x' union all select 3,'y' --------------生成数据-------------------------- ;WITH ym as ( select CAST(id AS VARCHAR)+','+name AS col FROM test ) SELECT DISTINCT stuff((select '|'+col from ym b
for xml path('')),1,1,'') 'col' from ym a----------------结果---------------------------- /* col ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,w|2,x|3,y */
IF OBJECT_ID(N'test') IS NOT NULL DROP TABLE test Go CREATE TABLE test(id INT IDENTITY(1,1),NAME VARCHAR(10)) INSERT INTO test SELECT 'w' UNION ALL SELECT 'x' UNION ALL SELECT 'y'----------------------------------------------查询-------------------------------------------- SELECT (SELECT CONVERT(VARCHAR,id)+ ','+name+'|' FROM dbo.Test FOR XML PATH('')) AS name
/* name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,w|2,x|3,y|(1 行受影响) */
修改一下,上面写的多了一个竖线IF OBJECT_ID(N'test') IS NOT NULL DROP TABLE test Go CREATE TABLE test(id INT IDENTITY(1,1),NAME VARCHAR(10)) INSERT INTO test SELECT 'w' UNION ALL SELECT 'x' UNION ALL SELECT 'y'----------------------------------------------查询-------------------------------------------- SELECT STUFF((SELECT '|'+CONVERT(VARCHAR,id)+ ','+name FROM dbo.Test FOR XML PATH('')),1,1,'') AS name
/* name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,w|2,x|3,y|(1 行受影响) */
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-05 15:41:39
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[name] nvarchar(2))
insert [test]
select 1,'w' union all
select 2,'x' union all
select 3,'y'
--------------生成数据--------------------------
;WITH ym as
(
select CAST(id AS VARCHAR)+','+name AS col
FROM test
)
SELECT DISTINCT
stuff((select '|'+col from ym b
for xml path('')),1,1,'') 'col'
from ym a----------------结果----------------------------
/*
col
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,w|2,x|3,y
*/
IF OBJECT_ID(N'test') IS NOT NULL
DROP TABLE test
Go
CREATE TABLE test(id INT IDENTITY(1,1),NAME VARCHAR(10))
INSERT INTO test SELECT 'w' UNION ALL SELECT 'x' UNION ALL SELECT 'y'----------------------------------------------查询--------------------------------------------
SELECT (SELECT CONVERT(VARCHAR,id)+ ','+name+'|' FROM dbo.Test FOR XML PATH('')) AS name
/*
name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,w|2,x|3,y|(1 行受影响)
*/
DROP TABLE test
Go
CREATE TABLE test(id INT IDENTITY(1,1),NAME VARCHAR(10))
INSERT INTO test SELECT 'w' UNION ALL SELECT 'x' UNION ALL SELECT 'y'----------------------------------------------查询--------------------------------------------
SELECT STUFF((SELECT '|'+CONVERT(VARCHAR,id)+ ','+name FROM dbo.Test FOR XML PATH('')),1,1,'') AS name
/*
name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,w|2,x|3,y|(1 行受影响)
*/
http://blog.csdn.net/dotnetstudio/article/details/17008693