22,302
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Id] int,[QueType] int,[QueTitle] nvarchar(25),[Answer] nvarchar(34))
Insert #T
select 21187,1,N'测试单选题',N'B、测试2测试' union all
select 21195,1,N'测试单选题',N'B、测试2测试' union all
select 21195,2,N'测试多选题',N'A、测试多1' union all
select 21195,2,N'测试多选题',N'C、测试多3测试时哟弄' union all
select 21195,3,N'测试填空题',N'打的' union all
select 21195,4,N'测试问答题',N'多大的顶顶顶顶湿哒哒' union all
select 21188,1,N'测试单选题',N'B、测试2大东方闪电' union all
select 21188,2,N'测试多选题',N'B、测试多2' union all
select 21188,2,N'测试多选题',N'C、测试多3阿斯顿发斯蒂芬' union all
select 21198,1,N'测试单选题',N'B、测试2都很反感路口监控了' union all
select 21198,2,N'测试多选题',N'B、测试多2' union all
select 21198,2,N'测试多选题',N'C、测试多3就很快很快就会' union all
select 21198,3,N'测试填空题',N'橘红颗粒很快就回家' union all
select 21198,4,N'测试问答题',N'客户即可很快过' union all
select 21192,1,N'测试单选题',N'B、测试212324564' union all
select 21192,3,N'测试填空题',null union all
select 21166,1,N'测试单选题',N'B、测试2' union all
select 21166,2,N'测试多选题',N'B、测试多2' union all
select 21166,3,N'测试填空题',N'111' union all
select 21166,4,N'测试问答题',N'224334343'
Go
--测试数据结束
--结果集可以这样使用,下边直接用就行
;WITH 你的结果集 AS (
SELECT * FROM #T
)
SELECT a.ID ,
STUFF(( SELECT ',' + Answer
FROM 你的结果集 b
WHERE a.id = b.Id
AND b.QueType = 1
FOR
XML PATH('')
), 1, 1, '') AS 测试单选题,
STUFF(( SELECT ',' + Answer
FROM 你的结果集 b
WHERE a.id = b.Id
AND b.QueType = 2
FOR
XML PATH('')
), 1, 1, '') AS 测试多选题,
STUFF(( SELECT ',' + Answer
FROM 你的结果集 b
WHERE a.id = b.Id
AND b.QueType = 3
FOR
XML PATH('')
), 1, 1, '') AS 测试填空题,
STUFF(( SELECT ',' + Answer
FROM 你的结果集 b
WHERE a.id = b.Id
AND b.QueType = 4
FOR
XML PATH('')
), 1, 1, '') AS 测试问答题
FROM 你的结果集 a
GROUP BY a.Id
ORDER BY a.Id
select * from(
select Id,QueType,QueTitle,(case when OptionId IS not null then isnull(OptionName,'')+isnull(OptionAnswer,'') else QueAnswer end)as answer from CALL_DAIL_PHONE_ITEM where QueType != 5) a
这是sql... 我要把结果弄到一张表里再用你的sql么--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Id] int,[QueType] int,[QueTitle] nvarchar(25),[Answer] nvarchar(34))
Insert #T
select 21187,1,N'测试单选题',N'B、测试2测试' union all
select 21195,1,N'测试单选题',N'B、测试2测试' union all
select 21195,2,N'测试多选题',N'A、测试多1' union all
select 21195,2,N'测试多选题',N'C、测试多3测试时哟弄' union all
select 21195,3,N'测试填空题',N'打的' union all
select 21195,4,N'测试问答题',N'多大的顶顶顶顶湿哒哒' union all
select 21188,1,N'测试单选题',N'B、测试2大东方闪电' union all
select 21188,2,N'测试多选题',N'B、测试多2' union all
select 21188,2,N'测试多选题',N'C、测试多3阿斯顿发斯蒂芬' union all
select 21198,1,N'测试单选题',N'B、测试2都很反感路口监控了' union all
select 21198,2,N'测试多选题',N'B、测试多2' union all
select 21198,2,N'测试多选题',N'C、测试多3就很快很快就会' union all
select 21198,3,N'测试填空题',N'橘红颗粒很快就回家' union all
select 21198,4,N'测试问答题',N'客户即可很快过' union all
select 21192,1,N'测试单选题',N'B、测试212324564' union all
select 21192,3,N'测试填空题',null union all
select 21166,1,N'测试单选题',N'B、测试2' union all
select 21166,2,N'测试多选题',N'B、测试多2' union all
select 21166,3,N'测试填空题',N'111' union all
select 21166,4,N'测试问答题',N'224334343'
Go
--测试数据结束
SELECT a.ID ,
STUFF(( SELECT ',' + Answer
FROM #T b
WHERE a.id = b.Id
AND b.QueType = 1
FOR
XML PATH('')
), 1, 1, '') AS 测试单选题,
STUFF(( SELECT ',' + Answer
FROM #T b
WHERE a.id = b.Id
AND b.QueType = 2
FOR
XML PATH('')
), 1, 1, '') AS 测试多选题,
STUFF(( SELECT ',' + Answer
FROM #T b
WHERE a.id = b.Id
AND b.QueType = 3
FOR
XML PATH('')
), 1, 1, '') AS 测试填空题,
STUFF(( SELECT ',' + Answer
FROM #T b
WHERE a.id = b.Id
AND b.QueType = 4
FOR
XML PATH('')
), 1, 1, '') AS 测试问答题
FROM #T a
GROUP BY a.Id
ORDER BY a.Id