写SQL代码 难

Question

if object_id('[users]') is not null drop table [users]
create table [users]([user_userid] int,[user_rolecode] varchar(31))
insert [users]
select 6001,',AAAA,' union all
select 6002,',AAAAA,AAAAB,' union all
select 6003,',AABBB,AABCA,AABDA,AABDB,AABCB,' union all
select 6004,',AACBB,AACCA,AACDA,AACDB,AACCB,' union all
select 6005,',AABBB,' union all
select 6006,',AACB,' union all
select 6007,',AADBB,' union all
select 6008,',AADCB,' union all
select 6009,',AAEBB,AAECA,' union all
select 6007,',AAFCB,AAFDA,' union all
select 6008,',AAFB,'drop function dbo.f_strcreate function dbo.f_str(@user_rolecode varchar(4)) returns varchar(8000)
as
begin
    declare @str varchar(1000)
    select @str = isnull(@str + '|','') + cast(user_rolecode as varchar) from users where left(user_rolecode,4)=@user_rolecode
    return @str
end
goselect '|'+replace(tt,',','')+'部门 |'+dbo.f_str(tt)+'|' from (select tt=left(user_rolecode,4),user_rolecode from users)t
group by tt
/*
|AAA部门 |,AAAA,|,AAAAA,AAAAB,|
|AAB部门 |,AABBB,AABCA,AABDA,AABDB,AABCB|,AABBB,|
|AAC部门 |,AACBB,AACCA,AACDA,AACDB,AACCB|,AACB,|
|AAD部门 |,AADBB,|,AADCB,|
|AAE部门 |,AAEBB,AAECA,|
|AAF部门 |,AAFCB,AAFDA,|,AAFB,|（所影响的行数为 6 行）*/drop table users

Answer 1

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-06-21 17:59:59
-------------------------------------
 
--> 生成测试数据: @tbDECLARE @tb TABLE(user_userid INT,user_rolecode VARCHAR(200))
INSERT @tb SELECT 6001,',AAAA,' 
UNION ALL SELECT 6002,',AAAAA,AAAAB,'  
UNION ALL SELECT 6003,',AABBB,AABCA,AABDA,AABDB,AABCB,' 
UNION ALL SELECT 6004,',AACBB,AACCA,AACDA,AACDB,AACCB,'  
UNION ALL SELECT 6005,',AABBB,' 
UNION ALL SELECT 6006,',AACB,' 
UNION ALL SELECT 6007,',AADBB,' 
UNION ALL SELECT 6008,',AADCB,' 
UNION ALL SELECT 6009,',AAEBB,AAECA,' 
UNION ALL SELECT 6007,',AAFCB,AAFDA,' 
UNION ALL SELECT 6008,',AAFB,' --SQL 查询如下:SELECT DISTINCT
    B.x.value('.','VARCHAR(3)') AS v
INTO #temp
FROM (
    SELECT
       CONVERT(XML,'<v>'+REPLACE(CASE WHEN user_rolecode LIKE ',%' THEN 
                                         CASE WHEN user_rolecode LIKE '%,'
                                                THEN STUFF(LEFT(user_rolecode,LEN(user_rolecode)-1),1,1,'')
                                                ELSE STUFF(user_rolecode,1,1,'')
                                         END
                                      WHEN user_rolecode LIKE '%,' THEN 
                                             LEFT(user_rolecode,LEN(user_rolecode)-1)
                                      ELSE user_rolecode
                                 END,
                            ',','</v><v>')+'</v>') AS v
    FROM @tb
) AS A
    CROSS APPLY A.v.nodes('//v') AS B(x);
SELECT *,rowid=ROW_NUMBER() OVER(PARTITION BY v ORDER BY user_userid) INTO #temp2
FROM #temp AS A
    JOIN @tb AS B
ON B.user_rolecode LIKE '%,' + A.v + '%,%';
DECLARE @maxid INT,@id INT;
SELECT @maxid=MAX(rowid) FROM #temp2;DECLARE @col VARCHAR(MAX);
SELECT @col = '',@id = 1;WHILE @id <= @maxid
    SELECT
        @col = @col + ',[' + CAST(@id AS VARCHAR) + ']',
        @id = @id + 1;SET @col = STUFF(@col,1,1,'');EXEC('SELECT v+''部门'' AS [部门],' + @col 
     + ' FROM (SELECT v,user_rolecode,rowid FROM #temp2) AS A'
     + ' PIVOT(MAX(user_rolecode) FOR rowid IN('+@col+')) AS pvt');--删除测试
DROP TABLE #temp,#temp2;/*
部门      1                                                                                                                                                                                                        2
------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AAA部门   ,AAAA,                                                                                                                                                                                                   ,AAAAA,AAAAB,
AAB部门   ,AABBB,AABCA,AABDA,AABDB,AABCB,                                                                                                                                                                          ,AABBB,
AAC部门   ,AACBB,AACCA,AACDA,AACDB,AACCB,                                                                                                                                                                          ,AACB,
AAD部门   ,AADBB,                                                                                                                                                                                                  ,AADCB,
AAE部门   ,AAEBB,AAECA,                                                                                                                                                                                            NULL
AAF部门   ,AAFCB,AAFDA,                                                                                                                                                                                            ,AAFB,(6 行受影响)
*/

Answer 2

汗.看错了,原来每一条user_rolecode都是相同的.

Answer 3

二楼的代码在sql2000下测试，报错:服务器: 消息 111，级别 15，状态 1，行 18
'CREATE FUNCTION' 必须是批查询中的第一条语句。
服务器: 消息 137，级别 15，状态 1，行 22
必须声明变量 '@user_rolecode'。
服务器: 消息 178，级别 15，状态 1，行 24
在此上下文中不能使用带有返回值的 RETURN 语句。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'users' 无效。
请回复，谢谢。