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
精彩评论