Merge Different Data Types in Query

I need to merge a numeric field (ID) with the varchar field (description) in
my query. Any idea how I can do this?
ID DESC
22 Text here
20 Text Here
Query Date to be: "(22) Text"
Thanks in advance!!You can use CAST, CONVERT or STR function to do this. For details of these
function, pl. refer to SQL Server Books Online.
SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
desc + '"'
FROM tbl ;
Anith|||Anith,
This is awesome. I am looking into this function as we speak. However, I
ran your suggestion and it does exactly what I want except it has 'Select '
before the data as part of the field. Anyway around this?
Example:
"Select 1999 Short Description" instead of "1999 short description"
"Anith Sen" wrote:

> You can use CAST, CONVERT or STR function to do this. For details of these
> function, pl. refer to SQL Server Books Online.
> SELECT '"' + CAST( id AS VARCHAR ) + SPACE( 3 ) +
> desc + '"'
> FROM tbl ;
> --
> Anith
>
>|||I think you have quotes messed up somewhere. Without a table DDL & sample
data it is hard to point out the issue. Also it may have something to do
with the wrapper code/interface/client programming language you may be
using. Here is an example to check it out in Query Analyzer:
CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
descr_ VARCHAR(50) NOT NULL );
INSERT tbl SELECT 1999, 'short description' ;
SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
FROM tbl ;
Anith|||Here is the Query I'm using in the view:
SELECT DISTINCT
TOP 100 PERCENT dbo.IssueSubType.IssueSubTypeID,
dbo.IssueSubType.IssueSubTypeTitle, dbo.IssueType.IssueTypeID,
'SELECT ' + CAST(dbo.Issue.IssueID AS VarChar) +
SPACE(2) + dbo.Issue.ShortDescription + '' AS Expr1, dbo.Issue.IssueID
FROM dbo.IssueType INNER JOIN
dbo.IssueSubType ON dbo.IssueType.IssueTypeID =
dbo.IssueSubType.IssueTypeID INNER JOIN
dbo.Issue ON dbo.IssueType.IssueTypeID =
dbo.Issue.IssueTypeID AND dbo.IssueSubType.IssueSubTypeID =
dbo.Issue.IssueSubTypeID
WHERE (dbo.IssueType.IssueTypeID = 16)
ORDER BY dbo.IssueSubType.IssueSubTypeID
"Anith Sen" wrote:

> I think you have quotes messed up somewhere. Without a table DDL & sample
> data it is hard to point out the issue. Also it may have something to do
> with the wrapper code/interface/client programming language you may be
> using. Here is an example to check it out in Query Analyzer:
> CREATE TABLE tbl ( id_ INT NOT NULL PRIMARY KEY,
> descr_ VARCHAR(50) NOT NULL );
> INSERT tbl SELECT 1999, 'short description' ;
> SELECT '"' + CAST( id_ AS VARCHAR ) + SPACE( 3 ) + descr_ + '"'
> FROM tbl ;
> --
> Anith
>
>|||Remove the part >> 'SELECT ' + << from your query.
Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
nothing beneficial & could cause optimization issues when used in complex
queries.
If an ordered resultset is the intention, consider using ORDER BY clause in
the query using the view instead.
Anith|||That was it. I tried various combinations of that but never that. Thanks
for your help!!!
"Anith Sen" wrote:

> Remove the part >> 'SELECT ' + << from your query.
> Also, as a suggestion, remove TOP 100 & ORDER BY from the view. It offers
> nothing beneficial & could cause optimization issues when used in complex
> queries.
> If an ordered resultset is the intention, consider using ORDER BY clause i
n
> the query using the view instead.
> --
> Anith
>
>

Mental Block

Can some please help with this SQL problem:
I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus
SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats INNER JOIN
dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
pleae help
Regards
John"John" <topguy75@.hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON
dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
John,
-- The query realigned and aliased for readability:
SELECT TOP 100 PERCENT
T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats AS PS1
INNER JOIN
dbo.tblCalendar_jp AS C1
-- Note right here there is no column name
-- On the right-hand side of the = operator,
-- just the table alias.
ON PS1.Mnth = C1
RIGHT OUTER JOIN
dbo.TblTerritory AS T1
ON PS1.Territory_Code = T1.Code
WHERE (T1.Manager = 'clive wallom')
AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
The above originally appeared as:
FROM dbo.jp_tblproject_stats
INNER JOIN
dbo.tblCalendar_jp
ON dbo.jp_tblproject_stats.Mnth
= dbo.tblCalendar_jpM
-- .<some column name goes here>
-- Probably should be .Mnth
Sincerely,
Chris O.|||John:
When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.
When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables
.
(the way Chris suggested you)
Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the null
s
say for example:
instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)
Either of the approach will give you expected result and will perform equall
y.
Abhishek
"John" wrote:

> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns
,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int
,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> John,
> -- The query realigned and aliased for readability:
> SELECT TOP 100 PERCENT
> T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats AS PS1
> INNER JOIN
> dbo.tblCalendar_jp AS C1
> -- Note right here there is no column name
> -- On the right-hand side of the = operator,
> -- just the table alias.
> ON PS1.Mnth = C1
> RIGHT OUTER JOIN
> dbo.TblTerritory AS T1
> ON PS1.Territory_Code = T1.Code
> WHERE (T1.Manager = 'clive wallom')
> AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> The above originally appeared as:
> FROM dbo.jp_tblproject_stats
> INNER JOIN
> dbo.tblCalendar_jp
> ON dbo.jp_tblproject_stats.Mnth
> = dbo.tblCalendar_jpM
> -- .<some column name goes here>
> -- Probably should be .Mnth
> Sincerely,
> Chris O.
>