在表中获得一行两行 [英] get one row of two row in a table

问题描述

我有下表：

i have below table :

ID   SalesID    Qt    unit
 1    1355      20    2500
 2    1355      20    5000
 3    1356      37    30000
 4    1356      37    20000
 5    1356      37    5000

现在我想查询以下结果

Now i want to get query for below result

SalesID    Qt1   unit1    Qt2   unit2    Qt3   unit3
 1355      20    2500     20    5000
 1356      37    30000    37    20000    37    5000

我需要回答这个问题请帮帮我

I need to answer this question please help me

推荐答案

测试一下：

Test it:

--declare destination table
DECLARE @data TABLE (ID INT IDENTITY(1,1), SalesID INT, Qt INT, unit INT)
--insert data
INSERT INTO @data (SalesID, Qt, unit)
SELECT 1355 AS SalesID, 20 AS Qt, 2500 AS unit
UNION ALL SELECT 1355, 20, 5000             
UNION ALL SELECT 1356, 37, 30000            
UNION ALL SELECT 1356, 37, 20000          
UNION ALL SELECT 1356, 37, 5000 

--declare temporary table to store RowNo
DECLARE @tmp TABLE (RowNo INT, ID INT, SalesID INT, Qt INT, unit INT)
--insert data
INSERT INTO @tmp (RowNo, SalesID, Qt, unit)
SELECT ROW_NUMBER() OVER(PARTITION BY Qt ORDER BY SalesID) AS RowNo, SalesID, Qt, unit
FROM @data

--pivot data
SELECT PT2.SalesID, SUM(PT4.[Qt1]) AS [Qt1], SUM(PT2.[unit1]) AS [unit1], SUM(PT4.[Qt2]) AS [Qt2], SUM(PT2.[unit2]) AS [unit2], 
		SUM(PT4.[Qt3]) AS [Qt3], SUM(PT2.[unit3]) AS [unit3]
FROM (
	SELECT RowNo, SalesID, [unit1], [unit2], [unit3]
	FROM (
		SELECT RowNo, SalesID, 'Qt' + CONVERT(VARCHAR(10), RowNo) AS QtDesc, Qt, 'unit' + CONVERT(VARCHAR(10), RowNo) AS UnitDesc, unit AS Unit
		FROM @tmp AS T
		) AS DT1
	PIVOT(MAX(Unit) FOR UnitDesc IN ([unit1], [unit2], [unit3])) AS PT1) AS PT2
LEFT JOIN (
	SELECT RowNo, SalesID, [Qt1], [Qt2], [Qt3]
	FROM ( 
		SELECT RowNo, SalesID, 'Qt' + CONVERT(VARCHAR(10), RowNo) AS QtDesc, Qt, 'unit' + CONVERT(VARCHAR(10), RowNo) AS UnitDesc, unit AS Unit
		FROM @tmp AS T
		) AS DT2
	PIVOT(MAX(Qt) FOR QtDesc IN ([Qt1], [Qt2], [Qt3])) AS PT3
	) AS PT4 ON PT2.RowNo = PT4.RowNo 
GROUP BY PT2.SalesID

输出：

Output:

1355    57  5000    57  10000   NULL    NULL
1356    57  60000   57  40000   37  5000

SQL Server：

SQL Server:

SELECT TOP 1 SalesID FROM table

我找到了解决方法。



i使用c＃代码。

I find solution for this.

i use c# code .

这篇关于在表中获得一行两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！