如何将Coloumn数据转换为行数据和Viceversa [英] How can I convert my Coloumn data to row data and Viceversa

问题描述

嗨



我有一张下面结构的桌子

Hi

I have a table with below structure

Elements 	 JAN 	FEB 	MAR 	APR 	MAY 	JUN
ABC       	100	200	300	400	500	600
XYZ       	200	300	400	500	600	700
PQR      	500	400	300	200	100	800

我希望转换为以下结构

and I want to convert to below structure

Month	 ABC 	XYZ	PQR
JAN	100	200	500
FEB	200	300	400
MAR	300	400	300
APR	400	500	200
MAY	500	600	100
JUN	600	700	800

这是最好的方法吗

我无法创建表格，我正在尝试查看



谢谢

Which is the best method to do this
I can not create a table, i am trying to make a view

Thanks

推荐答案

您是否熟悉 PIVOT 和 UNPIVOT ？





http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and- unpivot-table-examples / [ ^ ]

Are you Familar with PIVOT and UNPIVOT?


http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]

是的，King_Fisher是对的，您需要使用 PIVOT和/或UNPIVOT [ ^ ]运营商。



更多示例：

将行转换为列，列转换为sql server中的行 [ ^ ]

将列值转换为SQL查询中的行 [ ^ ]

CP知识库 [< a href =http://www.codeproject.com/search.aspx?q=pivot+tag%3asql&doctypeid=1%3b2%3b3%3b13target =_ blanktitle =New Window> ^ ]

Yes, King_Fisher is right, you need to use PIVOT and/or UNPIVOT[^] operators.

More examples:
convert rows to columns, columns to rows in sql server[^]
Converting Column values to Rows in SQL Query[^]
CP Knowledge Base[^]

试试这个，

CREATE TABLE [dbo].[Table_1]([Elements] [nvarchar](max) NULL,
	[Jan] [float] NULL,	[feb] [float] NULL,	[mar] [float] NULL,
	[Apr] [float] NULL,	[May] [float] NULL,	[Jun] [float] NULL
) ON [PRIMARY]

GO

insert into Table_1(Elements,Jan,feb,mar,Apr,May,Jun) values('ABC',100,200,300,400,500,600),
('XYZ',200,300,400,500,600,700),
('PQR',500,400,300,200,100,800);


SELECT Elements,Jan,feb,mar,Apr ,May ,Jun FROM Table_1

select col Month1,
  sum(case when Elements = 'ABC' then value end) ABC,
  sum(case when Elements = 'XYZ' then value end) XYZ,
  SUM(case when Elements = 'PQR' then value end) PQR
from
( select Elements, Jan value, 'JAN' col  from Table_1  union all
  select Elements, feb value, 'FEB' col  from Table_1  union all 
  select Elements, mar value, 'MAR' col  from Table_1  union all
  select Elements, Apr value, 'Apr' col from Table_1   union all 
  select Elements, May value, 'MAY' col from Table_1   union all 
  select Elements, Jun value, 'JUN' col  from Table_1
) x group by col ;

这篇关于如何将Coloumn数据转换为行数据和Viceversa的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！