adding text to the column data in a query result

If I had a table with 3 columns in it, named "ID", "TITLE" and "CLASS_ID" and, in a query result, I wanted to add a fourth column named "URL" that would be the result of concatenating a file name and the value in "ID", how would I do that?

so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028

could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"

I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?

Thanks in advance for your help.
roger

There are a few approaches, two different ways are shown below.

Chris

DECLARE @.URLTemplate VARCHAR(100)

SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'

SELECT ID,

TITLE,

CLASS_ID,

REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL

FROM ...

--or

DECLARE @.prefix VARCHAR(100)

SET @.prefix = '"hardcodedfilename.aspx?id='

SELECT ID,

TITLE,

CLASS_ID,

@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL

FROM ...

|||Thank you for your quick response!
I inserted the code you suggested into my existing, recursive function. The original function (which does work as it is) is:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN

SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML

DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.

Any suggestions? And thanks again for your help.

|||

I've made a couple of corrections, see the example below.

I wasn't sure whether you needed the trailing space in the following:

CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

If not, then simply replace the above with the following:

CAST(ID AS VARCHAR(10)) as "@.url",

Chris

CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @.prefix VARCHAR(100)

SET @.prefix = 'rightframe.aspx?s='

RETURN (

SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

CASE WHEN PARENT_ID=@.SceneID

THEN dbo.fn_WPMTREE(id)

END

FROM dbo.SCENE WHERE PARENT_ID=@.SceneID

FOR XML PATH('Scene'), TYPE

)

END

|||That works beautifully!

I can't thank you enough.

Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!

Again, Thank you.
|||

HI Chris,

I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:

Thanks

SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||

Change:

dbo.l_tblAgent.AgentDesc AS AgentDesc,

to:

( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,

|||Gr8...That works...Thanks a lot Arnie for your help...sql