create table tablename (id int, price numeric(10,2), dept varchar(10), num int)goinsert tablename
select
1, 11, 'A', 10
union all select
1, 11, 'B', 15
union all select
1, 15, 'A', 12
union all select
2, 10, 'C', 10
union all select
3, 10, 'D', 10gocreate function get_Dept_Num(
@id int,
@Price numeric(10,2)
)
returns varchar(100)
as
begin
declare @r varchar(100)
set @r=''
select @r=@r+' '+rtrim(dept)+' '+cast(num as varchar(10)) from tablename where id=@id and price=@price
return @r
endgoselect distinct id,price,dbo.get_Dept_Num(id,price) as value
from tablename
go
/*
结果:
id price value
----------- ------------ ----------------------------------------------------------------------------------------------------
1 11.00 A 10 B 15
1 15.00 A 12
2 10.00 C 10
3 10.00 D 10(所影响的行数为 4 行)
*/
select
1, 11, 'A', 10
union all select
1, 11, 'B', 15
union all select
1, 15, 'A', 12
union all select
2, 10, 'C', 10
union all select
3, 10, 'D', 10gocreate function get_Dept_Num(
@id int,
@Price numeric(10,2)
)
returns varchar(100)
as
begin
declare @r varchar(100)
set @r=''
select @r=@r+' '+rtrim(dept)+' '+cast(num as varchar(10)) from tablename where id=@id and price=@price
return @r
endgoselect distinct id,price,dbo.get_Dept_Num(id,price) as value
from tablename
go
/*
结果:
id price value
----------- ------------ ----------------------------------------------------------------------------------------------------
1 11.00 A 10 B 15
1 15.00 A 12
2 10.00 C 10
3 10.00 D 10(所影响的行数为 4 行)
*/
as
select distinct id,price,dbo.get_Dept_Num(id,price) as value
from tablename
SELECT [id] ,price,
SUM(CASE dept WHEN 'A' THEN num ELSE 0 END) AS deptA,
SUM(CASE dept WHEN 'B' THEN num ELSE 0 END) AS deptB,
SUM(CASE dept WHEN 'C' THEN num ELSE 0 END) AS deptC,
SUM(CASE dept WHEN 'D' THEN num ELSE 0 END) AS deptD
FROM tablename
GROUP BY [id] ,price