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