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 ,'hello' ,'同上时间' 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 ColumnCode
FROM Columntype t1
WHERE ISNULL(sec,'')<>'' GROUP BY TYPE,orgCode,sec
),
tr AS ( SELECT orgCode, TYPE, ColumnCode, RIGHT(ColumnCode,LEN(ColumnCode)-1) AS trs FROM t
),
ty AS ( SELECT DISTINCT TYPE,orgCode,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.orgname
最新文章
- PHP热身
- ASP。net treeview xml
- VCenter克隆虚拟机报错msg.snapshot.error-QUIESCINGERROR
- Pivot的SelectionChanged事件绑定到VM的Command
- 图解Android - Looper, Handler 和 MessageQueue
- [系统开发] Postfix 邮件管理系统
- Java:内部类
- JDBC之初识
- Android Http Server
- [转]iOS Tutorial – Dumping the Application Heap from Memory
- JAVA监听
- 做一个项目前搭建一个tabBar(一)框架
- php批量删除,批量操作
- 【转】java.lang.NoSuchMethodError: javax.persistence.Table.indexes()[Ljavax/persistence/Index;
- Django REST framework+Vue 打造生鲜超市(四)
- 用PULL解析器解析XML文件
- c/c++ 多线程 层级锁
- ASP.NET AJAX入门系列(4):使用UpdatePanel控件(一)
- 编程,将data段中的字符串转化成大写
- commit