combining two tables

If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3select A.col1,B.col1
FROM table_A A,table_B B

good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks

Combining two select statements

I have a SP returning the following result
The select statement for this is

Code:

SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC

name id count

case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73

i have another select stnat which returns like this
The select statement is

Code:Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name]
order by count(test1.[office name]) DESC

name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177

How can i combine this select stament with the SP, so that, i get a fourth column with

case1 226 320 74
case2 219 288 56
....................
.....................

Hope i am not confusing you all
Please help me, if someone knows how to combine this?

Thanks

Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.

Something like:

SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC

|||

I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.

combining two select staements

Folks

I have two select statements which gives two counts ie two numbers.

select 'count' = count(*) from account

select 'count1' = count(*) from employee

I want to combine these two select statements and write one select statement where I can get two columns 'count' and 'count1' with the respective values.

The result should be EX:

count count1
3 5

Thanksselect (select count(*) from account) as 'Count',
(select count(*) from employee) as 'Count1'

Combining Stored Procedures

I have two stored procedures

ALTER PROCEDURE dbo.qryCountOne
(@.inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @.inputID)

ALTER PROCEDURE dbo.qryCountTwo
(@.inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @.inputID)

What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?

Any help\pointers greatly appreciated,

Noel"Noel" <vbgooglegroups@.yahoo.com> wrote in message
news:1120752722.113719.37280@.g44g2000cwa.googlegro ups.com...
>I have two stored procedures
> ALTER PROCEDURE dbo.qryCountOne
> (@.inputID int)
> AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
> (dbo.TableOne.value = @.inputID)
> ALTER PROCEDURE dbo.qryCountTwo
> (@.inputID int)
> AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
> (dbo.TableTwo.value = @.inputID)
> What would be the best way to combine these two, so that I only have to
> make one database query, and the two values (CountOne, and CountTwo)
> will get returned to me?
>
> Any help\pointers greatly appreciated,
> Noel

Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?

create proc dbo.GetRowCounts
@.TableOneID int
@.TableOneCount int OUTPUT,
@.TableTwoID int,
@.TableTwoCount int OUTPUT
as
begin
select @.TableOneCount = count(*)
from dbo.TableOne
where col = @.TableOneID

select @.TableTwoCount = count(*)
from dbo.TableTwo
where col = @.TableTwoID
end

If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> If you have to use a result set instead of output parameters, then see
> "UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
> MSSQL, so if that is the real column name, you might want to consider
> changing it if possible - see "Reserved Keywords" in BOL.

It's listed among the "Future keywords". Given the record of SQL Server
I would not hold my breath until all those words become reserved.

T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> T-SQL has this funny notion of unreserved keywords, and they seem to grow in number with every release.<<

They got that idea from ANSI, which has such a list when we were
looking at the SQL3 working draft.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.<<
> They got that idea from ANSI, which has such a list when we were
> looking at the SQL3 working draft.

Nah, I was thinking of things like OUTPUT - which must have been around
since the 80s. OUTPUT is a keyword, but it's not reserved and you
can create a table or a column with that name, without any quoting.

But I assume you were thinking of the list of "Future keywords". That
does indeed seem like an ANSI list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's great, thanks!

Noel|||Yes, I agree it's unlikely to be a problem, but I generally prefer to
recommend that people follow best practices as documented by Microsoft.
For me, that's a better option than assuming that something has never
been a problem in the past, so it's going to be OK in the future (cf
the short article in this month's SQL Server Magazine on xp_reg% procs
behaviour in SP4).

Simon

combining select staements

Folks

I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.

select q_text Questions from question
where new_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '0'

Remember that all the queries returns more than 1 value.

I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.

Can any suggest me any other syntax??

Thanksselect [q_text Questions] from question
where new_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '0'|||I think it should be UNION ALL, since either condition may produce duplicate results. UNION will eliminate duplicates.sqlsql

Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

AS

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte

combining multiple select statements in a SP

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:

select name, age, title
from tablea

select name, age, title
from tableb

Could you combine these queries into a single result set?

Yes you can. You can use join or union to do this..

|||

Hi,

try like this:

select name, age, title
from tablea

UNION ALL //or use Union

select name, age, title
from tableb

Hope this helps

Combining Multiple rows into 1 row x 1 column

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name John Mike Dale

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

combining multiple rows in 1 row

how can i get a 1 row result set having multiple rows joined into 1 row
if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)
thx
samhamWant to show us the query? What would make them join together?|||see Using COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||He wants to combine multiple rows..

And you don't use COALESCE to build a comma delimited srting..

Is used so that any null value in the string does not blow away the results...

It's the ability to do SELECT @.x = @.X + col1

like...

DECLARE @.x varchar(8000)
SELECT @.x = ISNULL(@.x,'') + ISNULL(FirstName,'') FROM Employees
SELECT @.x

The coalesec trick allows you to eliminmate commas if the value in the column is Null

so you dont get 1,,2,3,4,,5|||He wants to combine multiple rows yes, he wants the values from multiple rows to be put into a comma-delimited string
And you don't use COALESCE to build a comma delimited srting damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
The coalesec trick allows you to eliminmate commas if the value in the column is Null yes, that's correct, that's what it does for the first row

rudy|||thx guys that's exactly what i wanted

i'll use the code from the article

DECLARE @.EmployeeList varchar(100)

SELECT @.EmployeeList = COALESCE(@.EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @.EmployeeList
--Results--

---
1, 2, 4

Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO

Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 ....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2

Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each

The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need
help with the select statement.

SQL Server MVP Anith Sen as a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Thanks for the info. My problem is the resulting data will be too large
for varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

Quote:

Originally Posted by

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

Yes, that is correct.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

combining fields (sort of)

Hi,
Hope this is neither too obvious or laughably impossible, else I'll
just have to be embarrased.
Is there any way to do this:

SELECT field1 if it is not NULL, else SELECT field2..??

The way this dbase is set up, I'm just about suicidal.
THANKS for any help!Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)|||Originally posted by andrewst
Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)

NVL did it! THANKS SO MUCH! I'd looked at that earlier but
the sql book I have only shows literals as being valid 'convert-to'
values. Shoulda just tried it. Thanks again!!!!!sqlsql

Combining fields

Not concatenation, more... err.. I don't know what you'd call it.

SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

(Apologies for the table/col names, this is all very temporary)

So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

Does that make sense?look into union if I understand your question corretly this should work|||I might not be understanding UNION correctly, but if I do, then I should have added that the two 'original data' tables have absolutely no similarities in structure. Does this make a difference?

Edit:
(sorry, that was a stupid thing to say and I've just realised why :rolleyes: Thanks :) )|||Use the Coalesce function:
SELECT DISTINCT
[C01241 Opened].[Col004] AS OpenerEmail,
Coalesce([C01241 External Data].[DMCEMAIL], [C01241 Internal Data].[Col15]) AS DataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

Combining date field and time field in a column

SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please reply to me at Babies001@.yahoo.co
ThankWhat datatypes are you using for storing your date and time values? If using
strings, then concatenate them and use CAST or CONVERT functions to convert
the concatenated value into a datetime value.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TM" <anonymous@.discussions.microsoft.com> wrote in message
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com...
SELECT RequireDate + ' ' + RequireTime AS date
FROM IN_Header
My Database:
Date Time
28/03/2004 01:34:09PM
After run SQL statement, my result become:
26/03/2004 01:34:09PM
Why my date minus two day? so what should i need to do? Urgent please reply
to me at Babies001@.yahoo.com
Thanks|||Regarding the Question, my database fiel
Field DataTyp
Date DateTim
Time DateTim
So my result become
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
What the code for convert the date and time together and my date will not minus two day
Can adding the source code inside
Thank
-- Narayana Vyas Kondreddi wrote: --
What datatypes are you using for storing your date and time values? If usin
strings, then concatenate them and use CAST or CONVERT functions to conver
the concatenated value into a datetime value
--
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"TM" <anonymous@.discussions.microsoft.com> wrote in messag
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com..
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please repl
to me at Babies001@.yahoo.co
Thanksqlsql

combining data on different sevvers

I need to combine data from two different tables on two MS SQL servers
running on the same LAN into a single SELECT or VIEW. Is this possible and
what would the syntax look like?
Thanks,
Charles
MTS, Inc.Yes, quite possible. Subject to the security on both servers allowing such a
ction.
Syntax is somewhat like this:
SELECT
a.Column1
, a.Column2
, b.Column5
, b.Column6
FROM Server1.MyDatabase.dbo.MyTable a
JOIN Server2.OtherDatabase.dbo.OtherTable b
ON a.KeyColumn = b.KeyColumn
WHERE ( a.CriteriaColumn = CriteriaA
AND b.CriteraColumn = CriteriaB
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message news:XIGCg.9735$gY6.3907@.n
ewssvr11.news.prodigy.com...
>I need to combine data from two different tables on two MS SQL servers
> running on the same LAN into a single SELECT or VIEW. Is this possible an
d
> what would the syntax look like?
>
> Thanks,
> Charles
> MTS, Inc.
>
>

combining containstable and freetexttable

i usetwocontainstable (one of them with 'formsof') andone freetexttable attributesin a select command and i want to combine them with the logical 'or'.

can i do this in the same command? what is the syntax?

my code :

select table1.field1,a.rank,b.rank,c.rank
from table1
containstable(table1,field,'"word"') as a
orcontainstable(table1,field,'formsof(inflectional(word)') as b)
orfreetexttable(table1,field,'word') as c
where table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]

the above syntax is wrong. i tried',' instead of 'or'but the results were not right.

thanks

Hi,

Two containstable condition can be connected by "or". But not for containstable and freetextable. Here's the sample for each one.

select table1.field1,A.Rankfrom table1InnerJoinCONTAINSTABLE(table1,field,'("word") OR ('FORMSOF(inflectional(word))')' )as Aon table1.id = A.[key]

 
select table1.field1, C.Rankfrom table1InnerJoinFREETEXTTABLE(talbe1,field,'word')as Con table1.id = C.[key]
 

Thanks.

combining 2 sql queries

hello everyone

there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :

select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5

and the other query

select x1, x2 from Table1 where table1.anything = 5

is there anyway??

Thank youyou have to union the two queries.
Note using union, each select has to have the same output columns (same columncoun, and naming). Using Dummy Columns will help. 'In order to place a condition you have to treat the Resultset from the Union query as an table. See example below.
e.g.
for your two selects the syntax will be:

SELECT * FROM
(
 select x1,x2,x3, table1.anything from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z
 UNION
 select x1, x2, '' as x3, anything from Table1
) t
WHERE t.anything = 5

Further information
http://www.really-fine.com/SQL_union.html
or
MSDN|||hii

im really thankfull for u, it worked perfectly :)

thank you again

best regards

combining 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.

|||

Hi,

I am thinking through this same process, and I could be wrong, but I don't think that this code is going to be accurate for determining weekdays. The reason is that if the first day you're counting is a Sunday (in this case, your DateintoSD), then you will have one more weekday than SQL is going to count. It will count the first week interval 6 days later, between Saturday and Sunday. You're multiplying your week DateDiff by 2, so you'll get that saturday and sunday subtracted from your total days of the month, but that first Sunday never gets subtracted. Am I wrong?

There is a solution I found elsewhere that involves building a calendar table in SQL, and if you Google that it will come up in your results. That's a bit more involved, however.

Andy

|||

A "calander" table should be added to any database as standard practice, loaded with weekday/end flags, public holidays and various date formats (these can be very useful when dealing with system interfaces)

combining 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.

Combining 2 records into 1

I have the follwing tables which contain error records and error reasons.
There are two erros entries for every error reason.
DDL
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS_REASON]
GO
CREATE TABLE [dbo].[INPUT_ERRORS] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemErrorItemType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ErrorMessageMessageLine] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
RECORD',' BACS',540)SET NOCOUNT ON
SET NOCOUNT OFF
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',23
6)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
SET NOCOUNT OFF
i would like to get this sresult returned for easy reading in a report
Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessageLi
ne
'217523' '16781' 'ORIGINAL RECORD' '19069'
'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
INVALID'
'217993' '25194' 'ORIGINAL RECORD' '7'
'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
INVALID'
'217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUNT
DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'Peter Newman wrote:
> I have the follwing tables which contain error records and error reasons.
> There are two erros entries for every error reason.
> DDL
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS_REASON]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemErrorItemType] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ErrorMessageMessageLine] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
> RECORD',' BACS',540)SET NOCOUNT ON
> SET NOCOUNT OFF
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',
236)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
> CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
> SET NOCOUNT OFF
> i would like to get this sresult returned for easy reading in a report
> Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
> ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessage
Line
> '217523' '16781' 'ORIGINAL RECORD' '19069'
> 'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' '7'
> 'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
> 'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUN
T
> DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'
Thanks for including the DDL. Unfortunately neither table has any keys!
Including an IDENTITY column means we can probably guess that that is a
key but an IDENTITY may not be enough to solve your problem. IDENTITY
should not be the only key of a table but you haven't shown us any
other so we can only guess. Here's one possibility:
SELECT licence, reportnumber,
LEFT(err1,50) AS erroritemtype1, RIGHT(err1,20) AS erroritemref1,
LEFT(err2,50) AS erroritemtype2, RIGHT(err2,20) AS erroritemref2
FROM
(SELECT licence, reportnumber,
MIN(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err1,
MAX(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err2
FROM input_errors
GROUP BY licence, reportnumber) AS T ;
David Portas
SQL Server MVP
--

combining 2 or more columns "as" one column in a select statement

hi guys,
how do u combine 2 columns as one in a select statement such as:
"select firstname,lastname from tablename"
where firstname and lastname are to be combined as single column called name
.
i had difficulty finding other resources for this issue.
Thanks in advance,
AdsString concatenation. The + symbol concatenates strings in SQL. So
your example would be something like:
select rtrim(firstname) + ' ' + rtrim(lastname) as [name] from
tableowner.tablename
or
select rtrim(lastname) + ', ' + rtrim(firstname) as [name] from
tableowner.tablename
The rtrim() function just trims off trailing whitespace (I'm assuming
there wont be any leading whitespace but you can take care of that too
with ltrim()), so you don't end up with something like "John
Smith " or "Smith , John ". The rest just depends exactly
what string you want at the end of it.
*mike hodgson*
http://sqlnerd.blogspot.com
ads wrote:

>hi guys,
>how do u combine 2 columns as one in a select statement such as:
>"select firstname,lastname from tablename"
>where firstname and lastname are to be combined as single column called nam
e.
>i had difficulty finding other resources for this issue.
>Thanks in advance,
>Ads
>
>|||SELECT fname + ' ' + lname as FulName from names
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ads" <ads@.discussions.microsoft.com> wrote in message
news:53BE5122-5439-4101-A221-E3FA011C6C24@.microsoft.com...
> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called
> name.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>|||Just to throw my $.02 in, I'd suggest doing NULL checks on any string concat
in SQL server.
So my version would look like:
SELECT ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '')
FROM TableName
Now if you want to get fancy you could throw a check in there to only output
the space if both names are present. At that point though I might use a udf
SELECT dbo.udfGetFullName(TablePK)
FROM TableName
Where the udf performs the logic required to properly format the name.
"ads" wrote:

> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called na
me.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>