存储过程求教。

[qq_38399333]

现有列如下：
id,
flag1,
flag2,
flag3,
flag4,
flag5,
flag6,
flag7,
flag8,
flag9,
flag10,
daycount1,
daycount2,
daycount3,
daycount4,
daycount5,
daycount6,
daycount7,
daycount8,
daycount9,
daycount10

如何写存储过程，将以上数据select成以下的结果？
id flag1 daycount1
id flag2 daycount2
id flag3 daycount3
id flag4 daycount4
id flag5 daycount5
id flag6 daycount6
id flag7 daycount7
id flag8 daycount8
id flag9 daycount9
id flag10 daycount10
我使用代码：

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

的出来的结果是
id flag1 flag2...flag10 daycount1 daycount2 daycount3...daycount10,整个都是一行，我需要select成10行

[zhr009]

多个left jion 嵌套，或者横表转竖表

[ChinaITOldMan]

can you use subquery to achieve

[二月十六]

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
......

[文盲老顾]

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','')