Concatenate 2 Integers Into 1 Varchar (SQL)

Yesterdays Just A Tip Felt so good, I thought I would do it again today.  Twice in one week.  You guys & gals get a great tip, while I get another notch under my belt.  Win.  Win.

Today's coding issue involves 2 Integer fields that need to be concatenated into 1 big field and that field needs to be Varchar.  To accomplish this, you will need to Cast each  Integer as a Varchar then concatenate them.

For Example, in an SQL table called tblPetOwners you have 2 fields (OwnerNumber & PetNumber)   which are Integers and wish to join them with an '&' in between them.  To do this, you have to CAST them both as Varchar before concatenating them.  Below is the best way I have came across to accomplish this task and I show you the data in the table, the code itself, followed by the results.

Table tblPetOwners Data:

OwnerNumber
PetNumber
223122
543224
643233
235432
332342
654523

Example Code:

SELECT CAST(OwnerNumber AS VARCHAR(10) ) 
+ ' & ' + 
CAST(PetNumber AS VARCHAR(10) ) OwnerAndPet, OwnerNumber, PetNumber 
FROM tblPetOwners

Results Data:

OwnerAndPet
OwnerNumber
PetNumber
223122 & 543224
223122
543224
643233 & 235432
643233
235432
332342 & 654523
332342
654523

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

Union & Count Together in A SQL Query

I know it has been a while since I have given you a good tip...So without further ado.

Today I had a situation where I needed to give a count of items that were across a couple of tables and involved a few unions in SQL.  Since my query took up about 2 pages, I figured I would make a simple version for all you folks out there reading this.  I hope it helps.

For Example, let us say you have two tables ( Results & ArchivedResults) and were needing to get a count on them both.  Let us also assume you didn't use a JOIN because of the different criteria in each SQL statement.  Based off those results you need to get a count.  Below is how this can be accomplished.

Example Code:

SELECT PullResults.Name, COUNT(*) 
FROM  (SELECT Name FROM Results
UNION ALL
SELECT Name FROM ArchivedResults) as PullResults
GROUP BY Name
ORDER BY Name

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...