Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck