如何在SQL Server 2008中进行转置矩阵 [英] How to do transpose matrix in SQL Server 2008

问题描述

如何以转置矩阵格式显示数据。



用于以下列格式显示数据的第一个SQL查询 -

  DECLARE   @ Category   AS  表（ID  INT  ，类别 NVARCHAR （ 100 ），PlanValue  DECIMAL （ 6 ， 2 ），ActValue  DECIMAL （ 6 ， 2 ））
 
  DECLARE   @ tblCnt   INT ， @row   INT  
 
  DECLARE   @cols   AS   NVARCHAR （MAX），< span class =code-sdkkeyword> @ catcols   NVARCHAR （max），
  @ query   AS   NVARCHAR （MAX）
 
  INSERT   INTO   @ Category （ID，Category，PlanValue）
  SELECT  l.ID，l.Lookup_Value，Sum（pg.PG_TotalAntExend * pg.PG_PercMadeInOman）/ 100  AS  PlanValue 
  FROM  TR_ICV_PlanningGoods pg 
  INNER   JOIN 查找l  ON  l.ID = pg.PG_category  AND  l.Lookup_Type  LIKE  ' 产品列表' 
 
  left  外部  join  TM_Supplier_Category sc  on  sc.SC_SupplierCategoryID = pg.SC_SupplierCategoryID 
  WHERE  sc.SC_SupplierCategoryID = 1 
 
  GROUP   BY  l.Lookup_Value，l.ID 
 
 更新  @ Category   SET  C.ActValue = PROD.actsumval  FROM   @ Category  C  INNER   JOIN  
（ SELECT  p.prod_category  AS  ID，SUM（p.prod_perc_madeinoman ）< span class =code-keyword> AS  actsumval  FROM  TR_ICV_Product p 
 
  left   outer   join  TM_Supplier_Category sc  on  sc.SC_SupplierCategoryID = p.mio_id 
 其中 sc.SC_SupplierCategoryID = 1 
 
  GROUP   BY  p.prod_category）PROD  ON  PROD.ID = C.ID 
 
 选择 * 来自  @ Category  

OutPut

表1-

ID 类别 PlanValue 实际值

 2527钻孔96.16 8.00 
 2528焊接45.00 30.00 

Resul t应该显示 -

我想显示如下格式的数据（Transpose Matrix）。意思是行应该转换为列，列应该转换为行。

< / pre> 
 钻井 焊接 
 PlanValue 96.16 45.00 
 AcualValue 8.00 30.00 
< pre>< / pre> 

解决方案

您可以使用PIVOT和UNPIVOT来实现这一目标。链接：

1. 使用PIVOT和UNPIVOT [ ^ ]

2. 使用sql的数据透视表服务器 [ ^ ]

3. Unpivot table in sql server [ ^ ]

Hi,
How to display data in transpose matrix format.

First SQL Queries for displaying data in below format-

DECLARE @Category AS TABLE (ID INT,Category NVARCHAR(100), PlanValue DECIMAL(6,2), ActValue DECIMAL(6,2))

DECLARE @tblCnt INT ,@row INT

DECLARE @cols AS NVARCHAR(MAX),@catcols NVARCHAR(max),
@query  AS NVARCHAR(MAX)

    INSERT INTO @Category(ID,Category,PlanValue)
    SELECT l.ID, l.Lookup_Value, Sum(pg.PG_TotalAntExend * pg.PG_PercMadeInOman)/100 AS PlanValue
    FROM TR_ICV_PlanningGoods pg
    INNER JOIN Lookup l ON l.ID=pg.PG_category AND l.Lookup_Type LIKE  'List Of Products'

    left outer join TM_Supplier_Category sc on sc.SC_SupplierCategoryID=pg.SC_SupplierCategoryID
    WHERE sc.SC_SupplierCategoryID=1

    GROUP BY l.Lookup_Value,l.ID

    UPDATE @Category SET C.ActValue = PROD.actsumval  FROM @Category C INNER JOIN
    ( SELECT p.prod_category AS ID, SUM(p.prod_perc_madeinoman) AS actsumval FROM TR_ICV_Product p

    left outer join TM_Supplier_Category sc on sc.SC_SupplierCategoryID=p.mio_id
where sc.SC_SupplierCategoryID=1

    GROUP BY p.prod_category) PROD  ON PROD.ID=C.ID

select * from @Category

OutPut
Table1-
ID Category PlanValue Actual Value

2527	Drilling	96.16	8.00
2528	Welding	        45.00	30.00

Result Should be display--
I want to display data like below format(Transpose Matrix).means rows should convert to column and column should convert to rows.

</pre>
                 Drilling                     Welding  
 PlanValue         96.16                          45.00
 AcualValue          8.00                          30.00
<pre></pre>

解决方案

Hi,

You could use PIVOT and UNPIVOT to achieve this. Links:
1. Using PIVOT and UNPIVOT[^]
2. Pivot table using sql server[^]
3. Unpivot table in sql server[^]

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