sql ¸÷ÖÖ×Ö·û´®ºÏ²¢·½·¨ µÚ¶þÖÖ·½·¨ºÃ(ÀûÓú¯Êý)

--3.3.1 ʹÓÃÓα귨½øÐÐ×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý¡£
--´¦ÀíµÄÊý¾Ý
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--ºÏ²¢´¦Àí
--¶¨Òå½á¹û¼¯±í±äÁ¿
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--¶¨ÒåÓα겢½øÐкϲ¢´¦Àí
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 from tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--ÏÔʾ½á¹û²¢É¾³ý²âÊÔÊý¾Ý
SELECT * from @t
DROP TABLE tb
/*--½á¹û
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
/*==============================================*/
--3.3.2 ʹÓÃÓû§¶¨Ò庯Êý£¬ÅäºÏSELECT´¦ÀíÍê³É×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý
--´¦ÀíµÄÊý¾Ý
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--ºÏ²¢´¦Àíº¯Êý
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) from tb GROUP BY col1
--ɾ³ý²âÊÔ
DROP TABLE tb
DROP FUNCTION f_str
/*--½á¹û
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
/*==============================================*/
--3.3.3 ʹÓÃÁÙʱ±íʵÏÖ×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý
--´¦ÀíµÄÊý¾Ý
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--ºÏ²¢