34,587
社区成员
发帖
与我相关
我的任务
分享
select
flag1,
flag2,
flag3,
flag4,
flag5,
flag6,
flag7,
flag8,
flag9,
flag10,
daycount1,
daycount2,
daycount3,
daycount4,
daycount5,
daycount6,
daycount7,
daycount8,
daycount9,
daycount10
from test where test.id = @id
declare @t table(id int identity,flag1 varchar(10),flag2 varchar(10),flag3 varchar(10),flag4 varchar(10),dc1 int,dc2 int,dc3 int,dc4 int)
insert into @t(flag1,flag2,flag3,flag4,dc1,dc2,dc3,dc4) values('aa','bb','cc','dd',10,15,12,43)
select a.id,a.value as flag,b.value as dc from (
select id,field,value from @t a unpivot(value for field in (flag1,flag2,flag3,flag4)) p
) a
inner join (
select id,field,value from @t a unpivot(value for field in (dc1,dc2,dc3,dc4)) p
) b on a.id=b.id and replace(a.field,'flag','')=replace(b.field,'dc','')
SELECT flag1 ,
daycount
FROM test
WHERE test.id = @id
UNION
SELECT flag2 ,
daycoun2
FROM test
WHERE test.id = @id
UNION
SELECT flag3 ,
daycoun3
FROM test
WHERE test.id = @id
......