SQL Server 2008 - 从多行数据创建列

Question

我有以下代码：

IF (OBJECT_ID('tempdb..#Data') IS NOT NULL)
BEGIN
DROP TABLE #Data
END
SELECT
t.Name, x.Time, x.Date, x.Total,
xo.DrvCommTotal, x.Name2, x.Street, x.Zip,
r.Route1
INTO 
#Data
FROM 
table1 xo WITH(NOLOCK)
LEFT JOIN  
Table2 t WITH(NOLOCK) ON t.ID = x.ID
LEFT JOIN 
Route1 r ON r.RouteID = x.RouteID
WHERE 
x.Client = 1
AND x.Date = '9/13/2018'
GROUP BY 
t.Name, x.Time, x.Date, x.Total, xo.DrvCommTotal, x.Name2,
x.Street, x.Zip, r.Route1
ORDER BY 
Route1
SELECT DISTINCT 
F.*, F2.NumOrders
FROM 
#Data F
LEFT JOIN 
(SELECT 
Route1, COUNT(*) NumOrders
FROM 
#Data
GROUP BY 
Route1) F2 ON F2.Route1 = F.Route1
LEFT OUTER JOIN 
(SELECT 
Street + ',' + Zip Stops, Time, RouteN1
FROM 
#Data
GROUP BY 
RouteNo1, street, Zip) F3 ON F3.Route1 = F.Route1
WHERE 
F.Route1 IS NOT NULL
ORDER BY 
F.Route1

它为我提供了路线和站点的列表。列 NumOrders 让我知道每条路线上有多少个订单。我需要将停靠点变成单独的列，我将标记 Stop1、Stop2 等，以便每条路线只有一行，并且所有信息都包含在一条路线的行中。

我目前正在使用临时表，因为数据太大了。我可以玩我的 SELECT 语句，而无需重新运行整个代码。

如何将每条路径的停靠点移动到列中？

Answer 1

哼哼。不太确定我是否理解这个问题，但听起来您想透视数据，以便路由分成列。如果是这样，我会使用 sql Pivot。以下是文档中的示例：

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM   
(SELECT PurchaseOrderID, EmployeeID, VendorID  
FROM Purchasing.PurchaseOrderHeader) p  
PIVOT  
(  
COUNT (PurchaseOrderID)  
FOR EmployeeID IN  
( [250], [251], [256], [257], [260] )  
) AS pvt  
ORDER BY pvt.VendorID; 

此外，这里是如何使用透视的链接： https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

由于临时表中已经拥有所有数据，因此您可以在出路时对其进行透视。