34,575
社区成员
发帖
与我相关
我的任务
分享
SQL2005
DECLARE @s NVARCHAR(4000)
SET @s='select [cNO]'
Select
@s=@s+',[type'+RTRIM([type])+']=max(case when [type]='+RTRIM([type])+' then [type] end),[pct'
+RTRIM([type])+']=sum(case when [type]='+RTRIM([type])+' then [pct] else 0 end),[exp_id'
+RTRIM([type])+']=stuff((SELECT '',''+RTRIM([exp_id]) FROM TAB WHERE [cNO]=a.[cNO] AND [Type]='+RTRIM([type])+' for xml Path('''')),1,1,'''')'
from Tab GROUP BY [type]
PRINT @s
EXEC(@s+' from Tab as a group by [cNO] order by cNO desc')
SQL2000需要用函數,SQL2005用XML
use Tempdb
go
--> -->
if not object_id(N'Tempdb..Tab') is null
drop table Tab
Go
Create table Tab([id] int,[cNO] int,[type] int,[pct] decimal(18,2),[exp_id] int)
Insert Tab
select 1,80105,2,1.00,6 union all
select 2,80105,3,1.00,7 union all
select 3,80105,1,.50,8 union all
select 4,80105,1,.50,9 union all
select 6,80104,1,.50,8 union all
select 7,80104,1,.50,9
Go
IF OBJECT_ID('F_str') IS NOT NULL
DROP FUNCTION F_str
go
CREATE FUNCTION F_str(@cNO INT,@Type INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @s NVARCHAR(100)
SET @s=''
SELECT @s=@s+','+RTRIM([exp_id]) FROM TAB WHERE Type=@Type AND [cNO]=@cNO
RETURN (STUFF(@s,1,1,''))
END
go
DECLARE @s NVARCHAR(4000)
SET @s='select [cNO]'
Select
@s=@s+',[type'+RTRIM([type])+']=max(case when [type]='+RTRIM([type])+' then [type] end),[pct'
+RTRIM([type])+']=sum(case when [type]='+RTRIM([type])+' then [pct] else 0 end),[exp_id'
+RTRIM([type])+']=dbo.F_str([cNO],'+RTRIM([type])+')'
from Tab GROUP BY [type]
PRINT @s
EXEC(@s+' from Tab group by [cNO] order by cNO desc')
/*
cNO type1 pct1 exp_id1 type2 pct2 exp_id2 type3 pct3 exp_id3
80105 1 1.00 8,9 2 1.00 6 3 1.00 7
80104 1 1.00 8,9 NULL 0.00 NULL NULL 0.00 NULL*/