mysql 行列转换

[乔巴大大]

现在有表 STK_FIN_Balance 字段：A0011,A0012,A001，ENDDATE，SYMBOL
sql语句：
SELECT
A0011,A0012,A001
FROM
STK_FIN_Balance
WHERE ENDDATE BETWEEN '2007-03-31'
AND '2007-12-31'
AND SYMBOL = '000042'

查询结果：A0011 A0012 A001
4.1 3.2 2.8
3.4 4.3 5.8
2.14 2.25 9.25
3.25 8.25 6.25


我想要的结果是： 2007-03-31 2007-06-30 2007-09-30 2007-12-31
A0011 4.1 3.4 2.14 3.25
A0012 3.2 4.3 2.25 8.25
A001 2.8 5.8 9.25 6.25

求大神帮我看看 感激不尽

[乔巴大大]

A0011 A0021 A001
4.1 3.2 2.8
3.4 4.3 5.8
2.14 2.25 9.25
3.25 8.25 6.25

[wwwwb]

贴建表及插入记录的SQL，及要求结果出来看看

SELECT
A0011,A0012,A001 ,ENDDATE
FROM
STK_FIN_Balance
WHERE ENDDATE BETWEEN '2007-03-31'
AND '2007-12-31'
AND SYMBOL = '000042'
存为VIEW1

SELECT 'A0011',MAX(IF(ENDDATE='2007-03-31',A0011,0)) AS `2007-03-31`,
MAX(IF(ENDDATE='2007-06-30',A0011,0)) AS `2007-06-30` ... FROM VIEW1
UNION ALL
SELECT 'A0012',MAX(IF(ENDDATE='2007-03-31',A0012,0)) AS `2007-03-31`,
MAX(IF(ENDDATE='2007-06-30',A0012,0)) AS `2007-06-30` ... FROM VIEW1
UNION ALL
SELECT 'A0013',MAX(IF(ENDDATE='2007-03-31',A0013,0)) AS `2007-03-31`,
MAX(IF(ENDDATE='2007-06-30',A0013,0)) AS `2007-06-30` ... FROM VIEW1
....

[乔巴大大]

[Quote=引用 2 楼 的回复:]

贴建表及插入记录的SQL，及要求结果出来看看

SELECT
A0011,A0012,A001 ,ENDDATE
FROM
STK_FIN_Balance
WHERE ENDDATE BETWEEN '2007-03-31'
AND '2007-12-31'
AND SYMBOL = '000042'
存为VIEW1

SELE……
[/Quote]

表早就有，其中ENDDATE只有固定的四种格式 yyyy-03-31,yyyy-06-30, yyyy-09-30, yyyy-12-31

我想要的格式：
2007-03-31 2007-06-30 2007-09-30 2007-12-31
4.1 3.4 2.14 3.25
3.2 4.3 2.25 8.25
2.8 5.8 9.25 6.25
就是把行列倒过来

[wwwwb]

最简单的就是将数据导出，在EXCEL中转置，SQL代码如上述

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943
MySQL交叉表
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...