我们如何在1列中添加两个colmn [英] How can we add two colmn in 1 column

问题描述

Ex： -

商品名称数量价格类别
 
 ABC 10 200 X 
 ABC 20 300 Y 
 ABC 40 900 Z 
 
 
需要结果： -  
 
物品名称数量价格类别
 
 ABC 30（10 + 20）500 （300 + 200）X + Y 
 ABC 40 900 Z 
 
在这种情况下，X，Y，Z是类别，我们在此使用group by子句。请建议查询。

解决方案

非常简单......



您可以通过运行获取所有类别X和Y行的查询来执行此操作，这是一个获取类别Z行并将它们组合在一起的单独查询。您不希望按类别分组，您需要按项目名称进行分组。例如：

  SELECT  [Item Name]，Sum（Qty） AS 数量，总和（价格） AS  PRICE，'  X + Y'  as 类别
  FROM  test 
  WHERE 类别 IN （'  X'，'  Y'）
  GROUP   BY  [项目名称] 
  UNION  < span class =code-keyword> ALL  
  SELECT  [Item Name]，Sum（Qty），Sum（Price），'  Z'  as 类别
 < span class =code-keyword> FROM  test 
  WHERE  Category = '  Z'  
  GROUP   BY  [商品名称] 
 

哪个给出了这些结果

项目名称数量价格类别
 ABC 30 500 X + Y 
 ABC 40 900 Z 

然而，看到你的评论

Quote：

我们知道购买的商品的总数量（信用卡和存款（在1栏中）和现金用于单独的库存。

然后您似乎希望结果进一步组合成类似

项目名称QTYXY PRICEXY QTYZ PRICEZ 
 ABC 30 500 40 900 

因此您可以根据[项目名称]将上述查询的结果加入到自身中，以便在一行中获得两组结果，例如

 WITH TempTable AS（
 SELECT Item，Sum（Qty）AS QTY，Sum（Price）AS PRICE，'X + Y'as Category 
 FROM test 
 WHERE Category IN（'X'，'Y'）
 GROUP BY项目
 UNION ALL 
选择项目，总和（数量），总和（价格），'Z'为类别
 FROM test 
 WHERE Category ='Z'
 GROUP BY Item 
）
 SELECT T1.ITEM，T1.QTY AS QtyXY，T1.PRICE AS PriceXY，
 T2。 QTY为QtyZ，T2.PRICE为PriceZ 
来自TempTable T1 
 INNER JOIN TempTable T2 ON T1.Item = T2.Item AND T2.Category ='Z'
 WHERE T1.Category =' X + Y'

Ex:-

Item Name       QTY          Price           Category
 
ABC             10           200              X
ABC             20           300              Y
ABC             40           900              Z


Need Result :- 

Item Name       Qty                Price           Category

ABC             30(10+20)        500(300+200)         X+Y
ABC             40                   900              Z

In this case X,Y,Z is category and we use group by clause on this. kindly suggest the query for this.

解决方案

Very simplistically ...

You can do this by running a query that gets all of the category X and Y rows, a separate query that gets the category Z rows and union them together. You don't want to group by category, you will need to group by the Item Name. For example:

SELECT [Item Name],Sum(Qty) AS QTY ,Sum(Price) AS PRICE,'X+Y' as Category
FROM test
WHERE Category IN ('X','Y')
GROUP BY [Item Name]
UNION ALL
SELECT [Item Name],Sum(Qty),Sum(Price),'Z' as Category
FROM test
WHERE Category = 'Z'
GROUP BY [Item Name]

Which gives these results

ITEM NAME	QTY	PRICE	CATEGORY
ABC 		30 	500 	X+Y 
ABC 		40 	900 	Z 

However, seeing your comment

Quote:

We know that total qty of a item which is purchase (credit and deposite (in 1 column)) and cash for seperate colmn.

then it seems that you want the results further combined into something like

ITEM NAME	QTYXY	PRICEXY	QTYZ PRICEZ
ABC 		30 	500 	40   900 

so you can join the results of the query above to itself based on [Item Name] to get both sets of results in a single row e.g.

WITH TempTable AS (
  SELECT Item,Sum(Qty) AS QTY ,Sum(Price) AS PRICE,'X+Y' as Category
  FROM test
  WHERE Category IN ('X','Y')
  GROUP BY Item
  UNION ALL 
  SELECT Item,Sum(Qty),Sum(Price),'Z' as Category
  FROM test
  WHERE Category = 'Z'
  GROUP BY Item
  )
SELECT T1.ITEM, T1.QTY AS QtyXY, T1.PRICE AS PriceXY, 
      T2.QTY as QtyZ, T2.PRICE as PriceZ
FROM TempTable T1 
INNER JOIN TempTable T2 ON T1.Item=T2.Item AND T2.Category='Z'
WHERE T1.Category = 'X+Y'

这篇关于我们如何在1列中添加两个colmn的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！