With table1 (sessionid,message,createtime)
As
(
Select 1, ' Hello ', ' 2014/5/6 ' UNION ALL
Select 1, ' word ', ' 2015/6/5 ' UNION ALL
Select 1, ' Hello ', ' 2015/7/4 ' UNION ALL
Select 2, ' Hello ', ' ditto time ' union ALL
Select 2, ' Hello ', ' ditto time '
)
Select SessionID,
Replace (CAST ((select ', ' +message from table1 T2
where T1.sessionid = T2.sessionid
FOR XML Path (")) as varchar (+)), ', ', ') as a 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 (+)), ', ', ' & ') 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 joins 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
SQL Server Hash string