在SQL中转置行/列 [英] Transposing the row/columns in SQL

问题描述

亲爱的所有人，

我有一种情况，需要计算按月购买，销售和利润/亏损.这个东西已经照顾好了.现在，我需要转置此数据以将月份和年份"显示为列标题以及购买"，销售"和利润/亏损".作为行长.月份和年份不固定.

我知道，可以通过使用temp/variable表使用长方法来完成. SQL中的数据透视"选项支持单个数据类型值.

但是，我想知道任何其他直接有助于转置行和列的方法

在此先感谢

Dear all,

I have a scenario where I need to calculate the Monthwise Purchases, sales and Profit/Loss. This thing is taken care of. Now I need to Transpose this data to show Month & year as column heading and Purchases, sales & Profit/Loss. as Row heads. The Month & year are not fixed.

I know that, it can be done using long method by using temp/variable table. The Pivot option in the SQL supports single data type values.

But, I would like to know about any other method which directly helps in transposing the rows & columns

Thanks in advance

推荐答案

看看此 [ ^ ]

我认为此查询对您有帮助

从表名中按月(TrDt)分组选择Sum(t1)，month(TrDt)；

i think this query will help u

select Sum(t1),month(TrDt) from tablename group by month(TrDt);

Hi Friend，
我找到了解决方法.
我们可以使用PIVOT Concept来做到.

我有一个名为"Purchase"的表，其中包含列(ID，DATE，Purchased，Sold，ProfitOrLoss)
我用参数@Year
创建了一个存储过程 我将显示购买项总数，已售项总数和一年的平均利润/亏损的月度明细

代码:-

Hi Friend,
I have found a solution.
We can do it by using PIVOT Concept.

I''m having a table named as Purchase with columns(ID,DATE,Purchased,Sold,ProfitOrLoss)
I have created a stored procedure with a parameter @Year
I''m going to show the month wise details of total number of purchased items,Total number of Sold items and Average Profit/Loss for a year

Code:-

--SP_GET_PURCHASE_ORDER 2011
ALTER PROCEDURE SP_GET_PURCHASE_ORDER
@YEAR INT
AS
BEGIN
SELECT 'PURCHASE' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PURCHASE,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(PURCHASE) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

UNION ALL

SELECT 'SALES' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT SALES,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(SALES) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

UNION ALL


SELECT 'Profit/Loss' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PROFITLOSS,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(AVG(PROFITLOSS) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

END

-请保持联系

阿拉文斯·G
SQL Developer，
Kadamba Technologies Pvt.Ltd，
钦奈
电子邮件:[e-mail]

--Please Keep me in Touch

Aravinth.G
SQL Developer,
Kadamba Technologies Pvt.Ltd,
Chennai
Email:- [e-mail]

这篇关于在SQL中转置行/列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！