SQL Server 2008 - create columns from multi row data

Question

I have the following code:

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

and it provides me with a list of routes and stops. The column NumOrders lets me know how many orders are on each route. I need the stops to become individual columns I will label Stop1, Stop2, etc. so that each route is only one row and all the information is contained on the row for one route.

I'm currently using the temp table because the data is so large. I can play with my SELECT statement without having to re-run the entire code.

How do I move the stops for each route into columns?

Answer 1

Hum.. Not quite sure I understand the question but it sounds that you want to pivot the data so that the routes break into columns. If so, I would use a sql Pivot. Here is an example from the documentation:

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; 

Also, here is the link to how to use pivot: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Since you already have all the data in your temp table, you could pivot that on the way out.