-測試環境
CREATE TABLE Table1(ID INT IDENTITY(1,1),NUM VARCHAR(10),STRING VARCHAR(10))
INSERT INTO Table1 SELECT 'G123','ABC'
UNION ALL SELECT 'H456','ABC'
UNION ALL SELECT 'G123','ABC'
UNION ALL SELECT 'K789','XYZ'
UNION ALL SELECT 'H456','XYZ'
UNION ALL SELECT 'K789','XYZ'
--函數
CREATE FUNCTION F_GETSTR(@NUM VARCHAR(10))
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @S VARCHAR(2000)
SET @S=''
SELECT @S=@S+','+T.STRING+'' FROM (SELECT DISTINCT STRING FROM Table1 WHERE NUM=@NUM) T
SET @S=STUFF(@S,1,1,'')
RETURN @S
END
--查詢
SELECT ID=(SELECT SUM(1)
FROM(SELECT NUM,STRING=DBO.F_GETSTR(NUM)
FROM Table1
GROUP BY NUM)T
WHERE T.NUM+'_'+T.STRING<=A.NUM+'_'+A.STRING)
,* FROM (SELECT NUM,STRING=DBO.F_GETSTR(NUM)
FROM Table1
GROUP BY NUM)A
--結果
NUM STRING
----- -----------
G123 ABC
H456 ABC,XYZ
K789 XYZ
DROP TABLE Table1
DROP FUNCTION F_GETSTR
posted on 2009-05-21 13:59
Werther 閱讀(825)
評論(0) 編輯 收藏 所屬分類:
15.SQL Server