行列转换续续

Question

create table tb(AreaID int, LevelID int,Area_Name Nvarchar(10), FatherID int)
insert into tb values(1 ,1 ,N'东区', 0 )
insert into tb values(2 ,1 ,N'南区', 0 )
insert into tb values(3 ,1 ,N'西区', 0 )
insert into tb values(4 ,1 ,N'北区', 0 )
goselect LevelID, [Area_Name] = stuff((select ',' + [Area_Name] from tb t where FatherID = 0 and LevelID = tb.LevelID for xml path('')) , 1 , 1 , '')
from tb where FatherID = 0
group by LevelIDdrop table tb/*
LevelID     Area_Name
----------- -------------------
1           东区,南区,西区,北区(1 行受影响)
*/

Answer 1

没明白我的意思，我还是接上帖提问
小梁的方法已经搞定，但是我假设我 区域与用户关系表 存了用户与区域的关系
比如东区 areaid 1    南区  areaid 2
那么存的这些数据出不来

Answer 2

tablename:tb_Base_Area 
AreaID  LevelID Area_Name      FatherID 
1        1      东区                0 
2        1      南区                0 
3        1      西区                0 
4        1      北区                0 
5        2      江苏                1 
6        2      浙江                1 
7        2      广东                2 
8        2      广西                2 
9        2      四川                3 
10       2      云南                3 
11       2      北京                4 
12       2      河北                4 
13       3      苏州                5 
14       3      无锡                5 
15       3      杭州                6 
table2 
ID    User_ID  Area_ID 
1      1        15 
2      1        14 
3      1        13 
4      2        5 
5      2        6 
6      2        7 
7      3        1
8      3        2
8      3        3查询出 
USer_ID      Area 
1            东区-浙江-杭州、东区-江苏-苏州,无锡 
2            东区-浙江,江苏、南区-广东 
3            东区,南区,西区

Answer 3

上去上去
tablename:tb_Base_Area 
AreaID  LevelID Area_Name      FatherID 
1        1      东区                0 
2        1      南区                0 
3        1      西区                0 
4        1      北区                0 
5        2      江苏                1 
6        2      浙江                1 
7        2      广东                2 
8        2      广西                2 
9        2      四川                3 
10      2      云南                3 
11      2      北京                4 
12      2      河北                4 
13      3      苏州                5 
14      3      无锡                5 
15      3      杭州                6 
table2 
ID    User_ID  Area_ID 
1      1        15 
2      1        14 
3      1        13 
4      2        5 
5      2        6 
6      2        7 
7      3        1 
8      3        2 
8      3        3 查询出 
USer_ID      Area 
1            东区-浙江-杭州、东区-江苏-苏州,无锡 
2            东区-浙江,江苏、南区-广东 
3            东区,南区,西区 

Answer 4

http://topic.csdn.net/u/20090303/11/214259f8-3fa3-456f-939a-56411d95a9b3.html
10楼显然被搂主忽视（感觉很不好）create table tb_Base_Area (
AreaID  int,
LevelID int,
Area_Name   varchar(10),   
FatherID int)
go
insert tb_Base_Area select
1, 1, '东区', 0 
union all select
2, 1, '南区', 0 
union all select
3, 1, '西区', 0 
union all select
4, 1, '北区', 0 
union all select
5, 2, '江苏', 1 
union all select
6, 2, '浙江', 1 
union all select
7, 2, '广东', 2 
union all select
8, 2, '广西',         2 
union all select
9, 2, '四川',         3 
union all select
10, 2, '云南',         3 
union all select
11, 2, '北京',         4 
union all select
12, 2, '河北',         4 
union all select
13, 3, '苏州',         5 
union all select
14, 3, '无锡',         5 
union all select
15, 3, '杭州',   6 
gocreate table tablename2 (
ID    int,
User_ID  int,
Area_ID int)
go
insert tablename2 select
1,      1,        15 
union all select
2,      1,        14 
union all select
3,      1,        13 
union all select
4,      2,        5 
union all select
5,      2,        6 
union all select
6,      2,        7 
union all select
7,      3,        1
union all select
8,      3,        2 
union all select
9,      3,        3 godeclare @t table (
USer_ID int,     
AreaID  int,
LevelID int,
FatherID int,
Area varchar(100)
)declare @t1 table (
USer_ID int,     
AreaID  int,
LevelID int,
Area varchar(100)
)insert @t
select 
a.User_ID ,     
b.AreaID,
b.LevelID,
b.FatherID,
Area=b.Area_Name 
from tb_Base_Area b,tablename2 a
where a.Area_ID =b.AreaID 
insert @t1
select 
    a.User_ID,FatherID,LevelID,Area=stuff(b.Area.value('/R[1]','nvarchar(max)'),1,1,'')
from 
    (select distinct User_ID,FatherID,LevelID from @t) a
Cross apply
    (select Area=(select N','+Area from @t where User_ID=a.User_ID and FatherID=a.FatherID and LevelID=a.LevelID For XML PATH(''), ROOT('R'), TYPE))bwhile exists (
    select 1 
    from @t1 t 
    where t.LevelID > 1
    ) 
begin    
    update t set 
        Area = f.Area_Name + '-' + t.Area  
        ,AreaID = f.FatherID 
        ,LevelID = f.LevelID 
    from @t1 t,tb_Base_Area f
    where t.AreaID = f.AreaID
    and t.LevelID > 1 
end    select 
    a.User_ID,Area=stuff(b.Area.value('/R[1]','nvarchar(max)'),1,1,'')
from 
    (select distinct User_ID from @t1) a
Cross apply
    (select Area=(select N','+Area from @t1 where User_ID=a.User_ID For XML PATH(''), ROOT('R'), TYPE))b--结果
User_ID     Area
----------- -------------------------------------------------------
1           东区-江苏-苏州,无锡,东区-浙江-杭州
2           东区-江苏,浙江,南区-广东
3           东区,南区,西区(3 行受影响)

Answer 5

改改函数..加上这句就行了..
IF NOT EXISTS(SELECT *
                  FROM [tb_Base_Area]
                  WHERE AreaID=@FatherID)
         SET @re=@Area_Name;
完整的:---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb_Base_Area]
IF OBJECT_ID('[tb_Base_Area]') IS NOT NULL DROP TABLE [tb_Base_Area]
CREATE TABLE [tb_Base_Area] (AreaID INT,LevelID INT,Area_Name VARCHAR(4),FatherID INT)
INSERT INTO [tb_Base_Area]
SELECT 1,1,'东区',0 UNION ALL
SELECT 2,1,'南区',0 UNION ALL
SELECT 3,1,'西区',0 UNION ALL
SELECT 4,1,'北区',0 UNION ALL
SELECT 5,2,'江苏',1 UNION ALL
SELECT 6,2,'浙江',1 UNION ALL
SELECT 7,2,'广东',2 UNION ALL
SELECT 8,2,'广西',2 UNION ALL
SELECT 9,2,'四川',3 UNION ALL
SELECT 10,2,'云南',3 UNION ALL
SELECT 11,2,'北京',4 UNION ALL
SELECT 12,2,'河北',4 UNION ALL
SELECT 13,3,'苏州',5 UNION ALL
SELECT 14,3,'无锡',5 UNION ALL
SELECT 15,3,'杭州',6
 
--> 生成测试数据: [tablename2]
IF OBJECT_ID('[tablename2]') IS NOT NULL DROP TABLE [tablename2]
CREATE TABLE [tablename2] (ID INT,User_ID INT,Area_ID INT)
INSERT INTO [tablename2]
SELECT 1,1,15 UNION ALL
SELECT 2,1,14 UNION ALL
SELECT 3,1,13 UNION ALL
SELECT 4,2,5 UNION ALL
SELECT 5,2,6 UNION ALL
SELECT 6,2,7 UNION ALL
SELECT 7,3,1 UNION ALL
SELECT 8,3,2 UNION ALL
SELECT 8,3,3 --SQL查询如下:GOCREATE FUNCTION f_ParentPath(@FatherID INT,@Area_Name VARCHAR(100))
    RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @re VARCHAR(MAX);
    
    IF NOT EXISTS(SELECT *
                  FROM [tb_Base_Area]
                  WHERE AreaID=@FatherID)
         SET @re=@Area_Name;
         
    WITH Liang AS
    (
        SELECT
            AreaID,
            CAST(Area_Name+'-'+@Area_Name AS VARCHAR(MAX)) AS Path,
            FatherID,
            level=1
        FROM [tb_Base_Area]
        WHERE AreaID=@FatherID
        UNION ALL
        SELECT
            A.AreaID,
            CAST(A.Area_Name+'-'+B.Path AS VARCHAR(MAX)),
            A.FatherID,
            B.level+1
        FROM [tb_Base_Area] AS A
            JOIN Liang AS B
                ON A.AreaID=B.FatherID
    )
    SELECT @re=Path
    FROM Liang 
    WHERE level=(SELECT MAX(level) FROM Liang);
    
    RETURN @re
END
GOSELECT 
    A.User_ID,
    STUFF(B.string,1,1,'') AS Area 
FROM (
    SELECT DISTINCT
        User_ID
    FROM [tablename2]
) AS A
    OUTER APPLY (
        SELECT string=(
                   SELECT
                       '、'+dbo.f_ParentPath(FatherID,STUFF(string,1,1,'')) AS [text()]
                   FROM (
                       SELECT
                           FatherID,
                           E.string
                       FROM (
                           SELECT DISTINCT
                               C.FatherID
                           FROM [tablename2] AS B
                               JOIN [tb_Base_Area] AS C
                                   ON B.Area_ID=C.AreaID
                           WHERE B.User_ID=A.User_ID
                       ) AS D
                           OUTER APPLY (
                               SELECT string=(
                                           SELECT
                                               ','+Area_Name AS [text()]
                                           FROM [tb_Base_Area] AS F
                                               JOIN [tablename2] AS G
                                                    ON F.AreaID=G.Area_ID
                                           WHERE D.FatherID=F.FatherID
                                               AND G.User_ID=A.User_ID
                                           FOR XML PATH('')
                                      )
                           ) AS E
                   ) AS C
                   FOR XML PATH('')
               )
    ) AS BGO
DROP TABLE [tb_Base_Area],[tablename2]
DROP FUNCTION f_ParentPath/*
User_ID     Area
----------- --------------------------------------------
1           东区-江苏-无锡,苏州、东区-浙江-杭州
2           东区-江苏,浙江、南区-广东
3           东区,南区,西区(3 行受影响)
*/

Answer 6

AreaID  LevelID Area_Name1    Area_Name2      Area_Name2    FatherID 
1        1      东区               东区2               东区2          0 
2        1      南区               南区2                南区2         0 
3        1      西区                ..                  ..            0 
4        1      北区                ..                  ..            0 
5        2      江苏                                                   1 
6        2      浙江                                                   1 
7        2      广东                                                   2 
8        2      广西                                                   2 
9        2      四川                                                   3 
10      2      云南                                                    3 
11      2      北京                                                    4 
12      2      河北                                                    4 
13      3      苏州                                                    5 
14      3      无锡                                                    5 
15      3      杭州                                                    6 
这张表我又加了几个字段，我想查出
User_ID    Area_ID        Area_Name1    Area_Name2    Area_Name3
5列
拼接方式跟上面的一样，Area_ID  只要显示成类似　　２，３，４　　就是跟某人关联的所有ID