开发者

Asp.Net VB or C# how to combine lines & columns based on unique primary field

I have been working on a web based report from an existing client/server apps mssql database. I have a working query, that pulls this information together from many tables, and can provide the query and a result set if necessary.

The results currently are similar to this.

ID | Name1 | Date1 | Veh  | PO    | Stops
_________________________________________  
1  | Bob 1 | 12/1  | Car  | 1234  |  4  
2  | Sam   | 12/3  | Car2 | 2245  |  3  
2  | Joe   | 12/4  | Van1 | 5568  |  2  
3  | Mel   | 1/4   | Van2 | 5678  |  5  
4  | Mel   | 2/2   | Car  | 3456  |  4  
4  | Sam   | 2/3   | Bus  | 4565  |  3  
4  | Joe   | 3/4   | Car  | 6766  |  3 

The Problem is that I don't know ahead of time if each ID will have 1 or many lines. I need the results to return a combined line, concatanate the data from several columns and add another.

Similar to

ID | Na开发者_如何学Cme1         | Date1 | Veh           | PO                |  Stops
1  | Bob 1         | 12/1  | Car           | 1234              |  4  
2  | Sam, Joe      | 12/3  | Car2, Van1    | 2245, 5568        |  5  
3  | Mel           | 1/4   | Van2          | 5678              |  5  
4  | Mel, Sam, Joe | 2/2   | Car, Bus, Car | 3456, 4565, 6766  |  10  


I've used a custom user defined function in SQL server that uses the COALESCE function in SQL Server. Here is an example.


Have a look at something like this (Full sql example)

DECLARE @Table TABLE(
    ID INT,
    Name1 VARCHAR(50),
    Date1 VARCHAR(6),
    Veh VARCHAR(50),
    PO VARCHAR(10),
    Stops INT
)

INSERT INTO @Table SELECT 1,'Bob 1','12/1','Car','1234',4   
INSERT INTO @Table SELECT 2,'Sam','12/3','Car2','2245',3   
INSERT INTO @Table SELECT 2,'Joe','12/4','Van1','5568',2   
INSERT INTO @Table SELECT 3,'Mel','1/4','Van2','5678',5   
INSERT INTO @Table SELECT 4,'Mel','2/2','Car','3456',4   
INSERT INTO @Table SELECT 4,'Sam','2/3','Bus','4565',3   
INSERT INTO @Table SELECT 4,'Joe','3/4','Car','6766',3  

SELECT  *
FROM    @Table

SELECT  t.ID,
        (
            SELECT  tIn.Name1 + ', '
            FROM    @Table tIn
            WHERE   tIn.ID = t.ID
            FOR XML PATH('')
        ) Name1,
        MIN(Date1) Date1,
        (
            SELECT  tIn.Veh + ', '
            FROM    @Table tIn
            WHERE   tIn.ID = t.ID
            FOR XML PATH('')
        ) Veh,
        (
            SELECT  tIn.PO + ', '
            FROM    @Table tIn
            WHERE   tIn.ID = t.ID
            FOR XML PATH('')
        ) PO,
        SUM(Stops) Stops
FROM    @Table t
GROUP BY t.ID
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜