数据分类

[dzxccsu]

表T数据

编码	属性
101	A01
101	B01
102	A01
102	B01
103	A01
103	A02
104	A01
104	B01
104	B02
105	A01
105	B01
106	A01
106	A02

想得到的结果：

结果1	
编码	属性
101	A01
101	B01
102	A01
102	B01
105	A01
105	B01
结果2	
103	A01
103	A02
106	A01
106	A02
结果3	
104	A01
104	B01
104	B02

求大神给SQL

[dzxccsu]

没人么 ？

[qq_17482963]

看不懂...
不是3个select语句吗

[dzxccsu]

这样表达吧
想得到的结果：
编码 属性 标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

[道玄希言]

;with tbl([编码], [属性]) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
),tbl2 as(
select [编码],
isnull([A01], 0) as [A01],isnull([A02], 0) as [A02],
isnull([B01], 0) as [B01],isnull([B02], 0) as [B02]
from (select [编码], [属性], 1 as i from tbl) as tbl
pivot
(
max(i) for [属性] in ([A01],[A02],[B01],[B02])
) as T
)

-- 结果1
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[B01]=1 and t.[A02]!=1 and t.[B02]!=1

-- 结果2
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[A02]=1 and t.[B01]!=1 and t.[B02]!=1

-- 结果3
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[B01]=1 and t.[B02]=1 and t.[A02]!=1

[dzxccsu]

感谢@yangb0803的回复
我的实际属性值是很多的，而且相同的数量也是不定的，
想得到的结果：
编码 属性 标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

[道玄希言]

;with tbl([编码], [属性]) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
),
tbl2 as
(
select [编码], STUFF((SELECT N','+QUOTENAME(tbl.[属性])
from tbl
where [编码]=T.[编码] for xml path('')
),1,1,N'') as [属性]
from (select distinct [编码] from tbl) T
),
tbl3 as
(
select ROW_NUMBER() OVER(order by MIN([编码])) as noid,
[属性]
from tbl2 group by [属性]
)
select
t1.[编码], t1.[属性], ltrim(STR(3000 + t3.noid)) as [标识]
from tbl as t1
left join tbl2 as t2
on t1.[编码] = t2.[编码]
left join tbl3 as t3
on t2.[属性] = t3.[属性]
order by t3.noid, t1.[编码]

------------------------------------------------------------------------
编码 属性 标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

[dzxccsu]

想到一起了，yangb0803
我的方法
;with tbl(id, value) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
)
,
tbl2
as
(
select id, [value] = stuff((select '|' + [value] from tbl t where id = tbl.id for xml path('')) , 1 , 1 , '')
from tbl
group by id

)


select id,e.value,idnum from
(
select id,value=convert(xml,'<root><v>'+replace(b.value,'|','</v><v>')+'</v></root>'),idnum from (
select [value],ROW_NUMBER()Over(ORDER BY [value] ) as idnum
from (
select distinct [value] from tbl2)a
)c
inner join tbl2 b on c.value=b.value)d
outer apply
(select value=C.v.value('.','nvarchar(100)') from d.value.nodes('/root/v')C(v))e

[道玄希言]

^_^

搞定了就好了， 呵呵