sqlServer拼结列字符串

with table1(sessionID,message,createTime)
as
(
select 1,‘hello‘,‘2014/5/6‘ union all
select 1,‘word‘,‘2015/6/5‘ union all
select 1,‘你好‘,‘2015/7/4‘ union all
select 2,‘同上时间‘ union all
select 2,‘同上时间‘
)
select sessionID,
replace(cast((select ‘,‘+message from table1 t2
where t1.sessionID = t2.sessionID
for xml path(‘‘)) as varchar(100)),‘,‘‘) as message,
max(createtime) createtime
from table1 t1
group by sessionID;

?

WITH t AS (SELECT sec,orgCode,TYPE,replace(cast((select ‘,‘+sec from Columntype t2 ?where t1.orgCode = t2.orgCode AND t1.type=t2.type?for xml path(‘‘)) as varchar(100)),‘&‘) as ColumnCodeFROM Columntype t1? WHERE ISNULL(sec,‘‘)<>‘‘ GROUP BY TYPE,sec?),?tr AS ( SELECT orgCode,ColumnCode,RIGHT(ColumnCode,LEN(ColumnCode)-1) AS trs FROM t ?),?ty AS ( SELECT DISTINCT TYPE,trs FROM tr ?),?tu AS (SELECT DISTINCT t.*,ct.orgname,ct.typeOrgCode FROM ty t LEFT JOIN Columntype ct ON t.orgCode=ct.orgCode? WHERE t.orgCode=ct.orgCode AND t.type=ct.type ?)?SELECT trs,typeOrgCode,tu.orgname FROM tu WHERE TYPE=‘dept‘ ORDER BY tu.orgnam

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!