---------------------------------------------
--> 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 行受影响)
*/