56,678
社区成员
发帖
与我相关
我的任务
分享行列转换求助
希望从左边转换为右边，我试了下堆一堆case when 能达到效果，有多少日期就得有多少条case when ....
不知有没有别的方法，谢谢
...全文
请发表友善的回复…
发表回复
ccdbg 2017-08-16
- 打赏
- 举报
感谢楼上2位，2楼建议也收到，一并感谢
Pact_Alice 2017-08-16
- 打赏
- 举报
create table T_Test(
Name varchar(10),
Date datetime,
I int
);
insert into T_Test
select 'A','2017-08-01',1000 union all
select 'B','2017-08-01',1000 union all
select 'C','2017-08-01',3000 union all
select 'D','2017-08-01',6000 union all
select 'E','2017-08-01',5000 union all
select 'F','2017-08-01',4000 union all
select 'G','2017-08-01',2000 union all
select 'A','2017-08-02',1000 union all
select 'B','2017-08-02',1000 union all
select 'C','2017-08-02',2000 union all
select 'D','2017-08-02',5000 union all
select 'E','2017-08-02',4000 union all
select 'F','2017-08-02',3000 union all
select 'G','2017-08-02',2000 union all
select 'A','2017-08-03',1000 union all
select 'B','2017-08-03',2000 union all
select 'C','2017-08-03',5000 union all
select 'D','2017-08-03',1000 union all
select 'E','2017-08-03',3000 union all
select 'F','2017-08-03',3000 union all
select 'G','2017-08-03',4000
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.Date = ''',
c.Date,
''', I, 0)) AS ''',
c.Date, ''''
)
)
INTO @sql
FROM T_Test c;
SET @sql = CONCAT('Select c.name, ', @sql,
' From T_Test c
Group by c.name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
以后问问题请把表与数据也发放出来
ACMAIN_CHM 2017-08-15
- 打赏
- 举报
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...
