一个比较难的查询，跪求大侠帮我看看，急啊
列明：id(主键自增) 0(bool) 1(bool) 2(bool) 3(bool)
1 1 0 0 0
2 0 1 1 1
查询结果为: 如果列为1，则增加一行，值为主键+'_'+当前为1的那个列名，试例和下面那样
1_0
2_1
2_2
2_3
跪求大侠指点
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [id] int, [0] bit, [1] bit, [2] bit, [3] bit);
insert #temp
select '1','1','0','0','0' union all
select '2','0','1','1','1'
--SQL:
select
result=LTRIM(id)+'_'+colname
from #temp a
UNPIVOT
(value FOR colname IN([0],[1],[2],[3])) b--列名不固定,用动态SQL拼即可
WHERE value = 1
/*
result
1_0
2_1
2_2
2_3
*/
create table #tb(id int,[0(bool)] bit,[1(bool)] bit,[2(bool)] bit,[3(bool)] bit)
insert into #tb
select 1,1,0,0,0
union all select 2,0,1,1,1
select aid=identity(int,1,1), id,col
into #tc
from (
select id,[0(bool)] as col from #tb where [0(bool)]=1
union all select id,[1(bool)] as col from #tb where [1(bool)]=1
union all select id,[2(bool)] as col from #tb where [1(bool)]=1
union all select id,[3(bool)] as col from #tb where [1(bool)]=1
)a
select cast(id as varchar)+'_'+cast((select count(*) from #tc b where b.aid<a.aid) as varchar)
from #tc a
drop table #tb,#tc
/*
1_0
2_1
2_2
2_3
*/