组合

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-07 11:19:29
--> Version: SQL Server 2005
---------------------------------------------
                                               
--> 测试数据: @tb
declare @tb table (A1 int,A2 int,A3 int,A4 int)
insert into @tb
select 1,0,1,1 union all
select 0,1,0,1 union all
select 1,0,0,0

select
A1=CASE WHEN A1=1 THEN 1 ELSE 0 END,
A2=CASE WHEN A2=1 THEN 1 ELSE 0 END,
A3=CASE WHEN A3=1 THEN 1 ELSE 0 END,
A4=CASE WHEN A4=1 THEN 1 ELSE 0 END,
A1A2=CASE WHEN A1=0 OR A2=0 THEN 0 ELSE 1 END,
A1A3=CASE WHEN A1=0 OR A3=0 THEN 0 ELSE 1 END,
A1A4=CASE WHEN A1=0 OR A4=0 THEN 0 ELSE 1 END,
A2A3=CASE WHEN A2=0 OR A3=0 THEN 0 ELSE 1 END,
A2A4=CASE WHEN A2=0 OR A4=0 THEN 0 ELSE 1 END,
A3A4=CASE WHEN A3=0 OR A4=0 THEN 0 ELSE 1 END,
A1A2A3=CASE WHEN A1=0 OR A2=0 OR A3=0 THEN 0 ELSE 1 END,
A1A3A4=CASE WHEN A1=0 OR A3=0 OR A4=0 THEN 0 ELSE 1 END,
A2A3A4=CASE WHEN A2=0 OR A3=0 OR A4=0 THEN 0 ELSE 1 END
from @tb

A1          A2          A3          A4          A1A2        A1A3        A1A4        A2A3        A2A4        A3A4        A1A2A3      A1A3A4      A2A3A4
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           0           1           1           0           1           1           0           0           1           0           1           0
0           1           0           1           0           0           0           0           1           0           0           0           0
1           0           0           0           0           0           0           0           0           0           0           0           0

(3 行受影响)

 

 

 

--> 生成测试数据: @tb
DECLARE @tb TABLE (A1 int,A2 int,A3 int,A4 int)
INSERT INTO @tb
SELECT 1,0,1,1 UNION ALL
SELECT 0,1,0,1 UNION ALL
SELECT 1,0,0,0

--SQL查询如下:

DECLARE @x xml;
SET @x = (SELECT * FROM @tb FOR XML PATH,TYPE);

CREATE TABLE #Tmp1(localname nvarchar(128),val int,rowid int,classid int);
INSERT #Tmp1
    SELECT *,
        classid=ROW_NUMBER()
                     OVER(PARTITION BY rowid
                            ORDER BY CAST(RIGHT(localname,PATINDEX('%[^0-9]%',
                                                            REVERSE(localname))-1)
                                            AS int))
    FROM (
        SELECT T.x.value('local-name(.)','nvarchar(128)') AS localname,
             T.x.value('.','int') AS val,
             T.x.value('for $i in .
                         return count($i/../../*[.<<$i/..])+1','int') AS rowid
        FROM @x.nodes('//row/*') AS T(x)
    ) AS A ;


CREATE TABLE #Tmp2(localname nvarchar(128),val int,
                   rowid int,classid int,total int,
                   path nvarchar(MAX),flag int);  
;WITH Liang AS
(
    SELECT *,total=val,path=CAST(localname AS nvarchar(MAX)),
         flag = classid
    FROM #Tmp1
    UNION ALL
    SELECT A.*,B.total+A.val,CAST(B.path + A.localname AS nvarchar(MAX)),
        flag = A.classid
    FROM #Tmp1 AS A
        JOIN Liang AS B
    ON A.rowid=B.rowid AND A.classid > B.classid
)
INSERT #Tmp2 SELECT * FROM Liang;


DECLARE @columns nvarchar(MAX);
SET @columns = N'';
SELECT @columns = @columns + ',MAX(CASE WHEN path = '''+path+''''
                           +' AND total > 0 THEN 1 ELSE 0 END) AS ['+path+']'
FROM #Tmp2 GROUP BY path;
SET @columns = STUFF(@columns,1,1,'');

EXEC('SELECT '+@columns+' FROM #Tmp2 GROUP BY rowid')


GO
DROP TABLE #Tmp1,#Tmp2;

/*
A1          A1A2        A1A2A3      A1A2A3A4    A1A2A4      A1A3        A1A3A4      A1A4        A2          A2A3        A2A3A4      A2A4        A3          A3A4        A4
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           1           1           1           1           1           1           1           0           1           1           1           1           1           1
0           1           1           1           1           0           1           1           1           1           1           1           0           1           1
1           1           1           1           1           1           1           1           0           0           0           0           0           0           0

(3 行受影响)

*/