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.
Chuck
Concatenating 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 "Divisions"
> 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 "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Wednesday, March 28, 2012
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
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
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.
Chuck
Concatenating 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 "Divisions"
> 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 "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
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.
Chuck
Concatenating 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 "Divisions"
> 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 "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
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 "Divisions"
> 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 "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
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 "Divisions"
> 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 "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
Friday, March 23, 2012
Help with SELECT
Can anyone point me in the right direction, the code below returns results but the date range imposed by the BETWEEN command doesn't seem to work, asall the results from the table are shown.
<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
AND date BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>
Regards
Tino
What format is your date column in the DB table? You can try using:
WHERE...AND date <='25/11/2007' AND date >= '26/11/2007'
Hope this helps,
Vivek
|||
Vivek
Do you mean the date type, if so its a datetime.
Thanks
Tino
|||Hitinomclaren ,
try like this
ANDCONVERT(DATETIME,'date',101) BETWEEN CONVERT(DATETIME,'2007-11-25',101) AND CONVERT(DATETIME,'2007-11-26',101)
or
AND (date > '2007-11-25' anddate < '2007-11-26' )
Regards,
Shri
Its a good practice to apply the conversion to all the participants in any comparison. So, your query should be like
"SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
ANDCONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)"
Hope this helps.
Thanks for suggestions, they didn't work for me
I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.
After 2 days at this one problem im trying not to get down but its really getting hard now
My database is SQLExpress and i've used the datetime datatype in the date colum on the DB. Im using VS2008 beta 2.
I have 20 test records in my DB and all have the date column filled in, im not using thetimepart but they are there in the DB and all default to midnight 00:00:00.
I dont know how to use stored procedures yet but im looking at the video tutorial form thelearn part of this site to hopfully get to grips with it, im assuming that the examples you have given me will work in either on the page (in the SqlDataSource as above) or in a Stored Procedure. Is there any Difference in the Syntax when switching between the two?
When I tried the suggestions you gave me using the Convert way I get ALL the records in the DB and when I use the < > way I get No records.
What am i doing wrong?
thx
tino
||| Can you post the table structure along with some sample records, so that we can understand your problem better ?
Here's the Table structure......
Coumn Name Data Type
gigId int ---> Primary Key, Is Identity-->Yes
venue varchar(50)
town varchar(50)
artist varchar(50
date datetime
I've looked at the datatypes available and there isn't just a date (without the time) must be an SqlExpress thing.
I cant even hard code 1 date in the SELECT command i.e.
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE (town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)AND (date = '25/11/2007') ">
thx tino
|||
tinomclaren:
Thanks for suggestions, they didn't work for me
I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.
Yes, you are missing something fundamental. :)
You cannot mix AND with OR as you have done and expect to get correct results.
In your example, all the ORs need to be within a set of () .
() must be used to delimit the logic boundaries of the ANDs and ORs.
Example:
If I tell you I will only date women who have red hair and blue eyes or green eyes, what do I mean?
Do I mean the women must have red hair - then they must also have blue eyes or green eyes?
Or would a blonde with green eyes qualify?
My original statement was ambiguous.
If, instead, I said, "I will only date women with red hair and (blue eyes or green eyes)", then the blonde with green eyes won't qualify.
If I said "I will only date women with (red hair and blue eyes) or green eyes" then the blonde with green eyes will qualify. So would a redhead with green eyes, for that matter.
HTH
|||
David,
Once again you have saved my life. I can only hope that one day I will be as experienced as you.
Two days for 2 brackets........I wont forget this.
Thankyou
Tino
|||
Thanks everyone for your help....All answers turned out to be correct......this forum is simply superb :)
For anyone with a similar problem here is the final code I used, which works perfectly....
<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE((town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)) AND CONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103) ORDER BY date asc">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>
Brackets inbold made the whole thing work correctly
thnx
Tino
Subscribe to:
Posts (Atom)