--sql 2000可以用函数哦 create FUNCTION udf_pin(@客户id INT ) RETURNS VARCHAR(100) AS BEGIN DECLARE @s VARCHAR(100) SELECT @s=isnull(@s+',','')+rtrim(购买产品ID) FROM tb WHERE 客户id=@客户id RETURN @s END; GO SELECT 客户id,dbo.udf_pin(客户id) AS 购买产品ID FROM tb GROUP BY 客户id
--sql2005一句话搞定 select 客户ID, 购买产品ID=stuff((select ','+rtrim( 购买产品id) from tb where t.客户id=客户id order by 购买产品id for xml path('')),1,1,'') from tb t group by 客户id
declare @客户id nvarchar(1000); select @客户id=isnull(@客户id+N',[','[') + ltrim(rtrim(convert(char,客户id)))+']' from (select distinct 客户id from TestTable) A --print @客户id declare @sql nvarchar(1000) set @sql=N'select pvt.* from TestTable pivot ( min([ 购买产品id ]) for 客户id in ('+@客户id +N') ) as pvt' --print @sql exec(@sql)
第2个回答 2010-08-24
一定要sql吗 存储过程可以吗
--过程test create or replace procedure test as cursor cur_id is select distinct id from tab; cursor cur_pid(v_id tab.id%type) is select pid from tab where id=v_id; begin dbms_output.put_line('客户'||' 产品'); for v_cur_id in cur_id loop dbms_output.put(v_cur_id.id||' '); for v_cur_pid in cur_pid(v_cur_id.id) loop dbms_output.put(' '||v_cur_pid.pid); end loop; dbms_output.put_line(''); end loop; end;