要怎么才能实现下图的效果？？
求各位大神
create table #Test(
ID int ,sNo varchar(15),num1 float,num2 float)
insert into #Test(id,sno,num1,num2) values(1,'A',1,2)
insert into #Test(id,sno,num1,num2) values(1,'B',1,1)
insert into #Test(id,sno,num1,num2) values(1,'C',1,3)
insert into #Test(id,sno,num1,num2) values(1,'D',1,2)
insert into #Test(id,sno,num1,num2) values(2,'A',3,0)
insert into #Test(id,sno,num1,num2) values(2,'B',1,0)
insert into #Test(id,sno,num1,num2) values(2,'C',2,0)
insert into #Test(id,sno,num1,num2) values(2,'D',4,0)
select * from #Test
drop table #Test
------解决方案--------------------
神一样的提问
------解决方案--------------------
s呵呵，估计没人能回答，问题太神。
------解决方案--------------------
看 DBA_Huangzj 的回帖学到的 Stuff ... for xml path 用法，结果就不贴了（表格表现不出换行，文本换行就错位了）
;WITH cte AS (
SELECT id,
sno+'='+convert(varchar(20),num1) t1,
sno+'='+convert(varchar(20),num2) t2
FROM #Test
),
c AS (
SELECT DISTINCT a.id,
Replace(Stuff((SELECT ','+t1
FROM cte b
WHERE b.id = a.id
for xml path('')),1,1,''),
',',char(13)+char(10)) 't1',
Replace(Stuff((SELECT ','+t2
FROM cte b
WHERE b.id = a.id
for xml path('')),1,1,''),
',',char(13)+char(10)) 't2'
FROM cte a
),
s AS (
SELECT id,
count(distinct num1) c1,
min(num1) num1,
count(distinct num2) c2,
min(num2) num2
FROM #Test
GROUP BY id
)
SELECT s.id,
CASE WHEN s.c1 = 1
THEN 'FULL='+convert(varchar(20),s.num1)
ELSE c.t1
END NUM1,
CASE WHEN s.c2 = 1
THEN 'FULL='+convert(varchar(20),s.num2)
ELSE c.t2
END NUM2
FROM s
JOIN c on s.id=c.id
------解决方案--------------------