---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-17 19:43:45
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,name VARCHAR(2))
INSERT INTO @T
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'AE' UNION ALL
SELECT 4,'CD' UNION ALL
SELECT 5,'BW' UNION ALL
SELECT 6,'CT' UNION ALL
SELECT 7,'EN' UNION ALL
SELECT 8,'AY'--SQL查询如下:SELECT
ROW_NUMBER() OVER(ORDER BY A.name) AS Rid,
REPLACE(B.name,' ',',') AS name
FROM (
SELECT LEFT(name,1) AS name
FROM @t
GROUP BY LEFT(name,1)
) AS A
CROSS APPLY(
SELECT name=(
SELECT name AS [data()]
FROM @T
WHERE LEFT(name,1)=A.name
FOR XML PATH('')
)
) AS B/*
Rid name
-------------------- ---------------------------
1 A,AE,AY
2 B,BW
3 CD,CT
4 EN(4 行受影响)*/
-- Author: liangCK 小梁
-- Date : 2008-11-17 19:43:45
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,name VARCHAR(2))
INSERT INTO @T
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'AE' UNION ALL
SELECT 4,'CD' UNION ALL
SELECT 5,'BW' UNION ALL
SELECT 6,'CT' UNION ALL
SELECT 7,'EN' UNION ALL
SELECT 8,'AY'--SQL查询如下:SELECT
ROW_NUMBER() OVER(ORDER BY A.name) AS Rid,
REPLACE(B.name,' ',',') AS name
FROM (
SELECT LEFT(name,1) AS name
FROM @t
GROUP BY LEFT(name,1)
) AS A
CROSS APPLY(
SELECT name=(
SELECT name AS [data()]
FROM @T
WHERE LEFT(name,1)=A.name
FOR XML PATH('')
)
) AS B/*
Rid name
-------------------- ---------------------------
1 A,AE,AY
2 B,BW
3 CD,CT
4 EN(4 行受影响)*/
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@name nvarchar(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+name from Tb where name like left(@name,1)+'%'
return @S
end
goselect distinct dbo.f_str(name) from tb
A,AE,AY
B,BW
CD,CT
EN
insert into @table select substring(name,1,1),' 'from table1 group by substring(name,1,1)
declare @n int
set @n=1
while @n<=(select count(*) from table1) --用循环查找
begin
update @table set express=express+','+(select name from table1 where id=@n)
where name =(select substring(name,1,1) from table1 where id=@n)
set @n=@n+1
end
select * from @table
------------------------------
a ,a,ae,ay
b ,b,bw
c ,cd,ct
e ,en