如何用SQL 结果拼写成一个值
在SQL里面如何写得到以下的值,
PI12060001,PI12070002,PI12080004,PI12080005,PI1208000
用函数返回值
数据集如下
select distinct scno from sccidtl
SCNO
PI12060001
PI12070002
PI12080004
PI12080005
PI12080007
[解决办法]
select stuff((select distinct ','+scno
from sccidtl for xml path('')),1,1,'') 'scnos'
----------------------------
-- Author :DBA_Huanzj（發糞塗牆）
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
--Oct 19 2012 13:38:57
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([scno] varchar(53))
insert [huang]
select 'PI12060001,PI12070002,PI12080004,PI12080005,PI1208000'
--------------开始查询--------------------------
select
SUBSTRING([scno],number,CHARINDEX(',',[scno]+',',number)-number) as [scno]
from
[huang] a,master..spt_values
where
number >=1 and number<len([scno])
and type='p'
and substring(','+[scno],number,1)=','
----------------结果----------------------------
/*
scno
-----------------------------------------------------
PI12060001
PI12070002
PI12080004
PI12080005
PI1208000
*/
declare @tab table(val varchar(10))
insert into @tab
select 'PI12060001' union all
select 'PI12070002' union all
select 'PI12080004' union all
select 'PI12080005' union all
select 'PI12080007' union all
select 'PI12060001' union all
select 'PI12080007'
select stuff((select distinct ','+val from @tab for xml path('')),1,1,'')
