请问

Question

数据如下：fitem   stock                                                                                                                      
100    a01
100    b02
100    c03
100    d04想得到以下数据
fitem   stock            count
100     a01,b02,c03,d04   4
count 列是100产品所对应的stock仓库的个数请问语句怎么写？

Answer 1

create function getValue(@fitem int)
returns varchar(100)
as
begin
   declare @sql varchar(100)
   select @sql=isnull(@sql+',','')+ stock from [Table] where fitem=@fitem
   return @sql
end
go
select fitem,dbo.getValue(fitem),count(1) [count] from [Table] group by fitem

Answer 2

2000写个函数返回,
2005可以使用xml,
count就直接使用count(1)就可以得到了,
详细参考风的整理贴
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 3

if object_id('tb') is not null drop table tb
go
CREATE TABLE tb(col1 varchar(10),col2 varchar(10))
INSERT tb SELECT '100','a01'
UNION ALL SELECT '100','b02'
UNION ALL SELECT '100','c03'
UNION ALL SELECT '100','d04'
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
SELECT col1,col2=dbo.f_str(col1),count(1) as num FROM tb GROUP BY col1/*
col1       col2                                                                                                 num         
---------- ---------------------------------------------------------------------------------------------------- ----------- 
100        a01,b02,c03,d04                                                                                      4（所影响的行数为 1 行）
*/

Answer 4

IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE tb (fitem int,stock varchar(50))
insert into tb select 100,'a01'
insert into tb select 100,'b02'
insert into tb select 100,'c03'
insert into tb select 100,'d04'
go
drop function f_hb
go
create function f_hb(@fitem int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+stock from tb where fitem=@fitem group by stock
return @sql
end
go
select fitem,dbo.f_hb(fitem) as stock 
from tb group by fitemfitem stock
100 a01,b02,c03,d04

Answer 5

select fitem,dbo.f_hb(fitem) as stock ,count(1) as con
from tb group by fitemfitem stock con
100 a01,b02,c03,d04 4