我如何...在mysql中将列表转换为行副 [英] How do i...transporse a column table to row vice in mysql
本文介绍了我如何...在mysql中将列表转换为行副的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子:
品牌价格颜色尺寸
addidas 500黑色7
耐克400 Blue 8
Reebok 200 Red 9
我需要将其转置为:
<前lang =text> AttributeName AttributeValue
品牌addidas
价格500
颜色黑色
尺寸7
品牌Nike
价格400
颜色蓝色
尺码8
品牌Reebok
价格200
颜色红色
尺寸9
我尝试了什么:
还没有找到任何来源转置到行到列副解决方案
解决方案
您好,
根据您的要求,我在下面做出解决方案.Tho呃这不是好方法。
//创建表
CREATE TABLE a
(
id int,
brand varchar(20),
price int,
color varchar(20),
size int
);
//将行插入表格
INSERT INTO a(id,品牌,价格,颜色,尺寸)
VALUES
(1,'addidas',500, '黑',7),
(2,'耐克',400,'蓝',8),
(3,'Reebok',200,'红',6);
查询以获得所需的输出
从
中选择AttributeName,AttributeValue (选择a.id,1为'colid','品牌'为'AttributeName',品牌为'AttributeValue'来自
联盟
选择a.id,2为'colid','price' ,价格来自
联盟
选择a.id,3作为'colid','color',颜色来自
联盟
选择a.id,4作为'colid' ,大小,大小从
)x顺序x.id,x.colid
这里colid变量用于获取所需的属性值订单。
输出可在此链接中找到
谢谢
I have a table like:
Brand Price Colour Size
addidas 500 Black 7
Nike 400 Blue 8
Reebok 200 Red 9
I need to transpose it like:
AttributeName AttributeValue
Brand addidas
Price 500
Colour Black
Size 7
Brand Nike
Price 400
Colour Blue
Size 8
Brand Reebok
Price 200
Colour Red
Size 9
What I have tried:
No source still found only found to transpose to row to column vice solutions
解决方案
Hello,
As per your requirement I make below solution.Though it is not good approach.
//Create Table CREATE TABLE a ( id int, brand varchar(20) , price int, color varchar(20), size int ) ; //Insert rows into table INSERT INTO a (id,brand,price,color,size) VALUES (1,'addidas', 500 ,'Black', 7), (2,'Nike', 400 ,'Blue' ,8), (3,'Reebok', 200 ,'Red' ,6) ;
Query to get the required output is
select AttributeName,AttributeValue from (select a.id,1 as 'colid','brand' as 'AttributeName', brand as 'AttributeValue' from a union select a.id,2 as 'colid','price' , price from a union select a.id,3 as 'colid','color',color from a union select a.id ,4 as 'colid','size',size from a )x order by x.id,x.colid
Here colid variable is used to get the attribute value by required order.
Out put is available in This link
Thanks
这篇关于我如何...在mysql中将列表转换为行副的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文