Question

SQL表行列转换后得到新的查询数据

我有一张sql表如下
Code Name Product S201101 S201102 S201103 S201104
XQ0001 AA P1 20 40 50 0
XQ0001 AA P2 32 3 0 78
XQ0002 BB P1 32 34 9 3
XQ0002 BB P2 3 3 5 3
-------------------------------------------------------------------------------
我现在想显示为
Year Code Name P1 P2

S201101 XQ0001 AA 20 32
S201102 XQ0001 AA 40 3
S201103 XQ0001 AA 50 0
S201104 XQ0001 AA 0 78
S201101 XQ0002 BB 32 3
S201102 XQ0002 BB 34 3
S201103 XQ0002 BB 9 5
S201104 XQ0002 BB 3 3

Answer 1

select S201101 year,Code,Name,case when Product=P1 then S201101 else 0 end P1,case when Product=P2 then S201101 else 0 end P2 from table1
union all
select S201102 year,Code,Name,case when Product=P1 then S201102 else 0 end P1,case when Product=P2 then S201102 else 0 end P2 from table1
union all
select S201103 year,Code,Name,case when Product=P1 then S201103 else 0 end P1,case when Product=P2 then S201103 else 0 end P2 from table1
union all
select S201104 year,Code,Name,case when Product=P1 then S201104 else 0 end P1,case when Product=P2 then S201104 else 0 end P2 from table1

Answer 2

select 'S201101' as year, code, name, product as P1, S201101 as P2
 from table
union all
select 'S201102' as year, code, name, product as P1, S201102 as P2
union all
select 'S201103' as year, code, name, product as P1, S201103 as P2
union all
select 'S201104' as year, code, name, product as P1, S201104 as P2

追问

这个代码不对的

追答

select A.year, A.code, A.name, sum(A.P1) P1, sum(A.P2) P2

from
(

select 'S201101 'as year, code,name, S201101 as P1, null as P2
from table where product = 'P1'

unionall
select 'S201102' as year, code,name, S201102 as P1, null as P2
from table where product = 'P1'
unionall
select 'S201103' as year, code,name, S201103 as P1, null as P2
from table where product = 'P1'
unionall
select 'S201104' as year, code,name, S201104 as P1, null as P2
from table where product = 'P1'
union all
select 'S201101 'as year, code,name, null as P1 , S201101 as P2

from table where product = 'P2'

unionall
select 'S201102' as year, code,name, null as P1 , S201102 as P2
from table where product = 'P2'
unionall
select 'S201103' as year, code,name, null as P1 , S201103 as P2
from table where product = 'P2'
unionall
select 'S201104' as year, code,name, null as P1 , S201104 as P2
from table where product = 'P2'
) A
Group by A.year, A.code, A.name

追问

有没有比较好的方法，我的数据有4万多条，列出的只是部分

追答

行多没有关系的，只要列不多就可以。

追问

列也多

Answer 3

你这个用三层循环就能搞定 分别对S201101（2,3,4...），code，P1（2,3,4,...）循环

追问

具体怎么做？

追答

for(i=1,i<4,i++)
for(j=1,j<2,j++)
for(n=1,n<2,n++)
select S20110+i as year, XQ000+j as code,name, product as P+n

追问

我的数据只是一部分，有没有比较好的方法

追答

不管你的数据有多少条，你只要按照上面的方法、对比你的最大数修改一下i<多少、j<多少、n<多少及OK了