如何将第一列的数据作为行提取到标题和最后一列，因为行属于SQL查询中的标题？ [英] How to fetch the data of first column as row to the header and last column as row belongs to header in SQL query?

问题描述

我在数据库中有3个表1.Warehouse，2.Product，3。Stock和我想从这三个表中获取数据。

Frirst Column是仓库名称，希望是库存中可用数量的标题。

第二列是无法复制的产品名称。

第三列是库存中的可用数量，必须与第一列仓库名称一起转换为行。 />


输出

I have 3 table in database 1.Warehouse, 2.Product, 3. Stock and i want to fetch the Data from these three Table like.
Frirst Column are the Warehouses names want be the header of available Quantity left in Stock.
Second Column is The Product Names which can not be duplicated.
Third Column is Available Quantity in Stock which have to convert as row along with First Column warehouse names.

output

Warehouse Name   |  Product Name  |  Available Quantity
Warehouse 1      |   Shampoo     |    50
Warehouse 2      |   Shampoo     |    100
Warehouse 3      |   Shampoo     |    40
Warehouse 1      |   Soup        |    50
Warehouse 2      |   Soup        |    80
Warehouse 3      |   Soup        |    120

我想要预期的产量

I want expected Output

Product Name |   Warehouse1 Qty | Warehouse2 Qty | Warehouse3 Qty |  Total 
Shamoo       |    50            |       100      |     40        |  190
Soup         |    40            |       80       |     120        |  240

我尝试过：

What I have tried:

SELECT       WareHouse.WareHouse_Name,
              Product.Cat_Name 'Product Name',
               SUM(Stock.Stock_In-Stock.Stock_Out) 'Available Quantity'

FROM            Stock INNER JOIN
                         Product_Catagory ON Stock.Cat_Id = Product_Catagory.Cat_ID INNER JOIN
                         WareHouse ON Stock.Warehouse_ID = WareHouse.WareHouse_ID

			GROUP BY    Product_Catagory.Cat_Name,WareHouse.WareHouse_Name

推荐答案

您需要使用 PIVOT SQL函数。谷歌是你的朋友。

You need to use the PIVOT SQL function. Google is your friend.

这篇关于如何将第一列的数据作为行提取到标题和最后一列，因为行属于SQL查询中的标题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！