CODEWITHSUNDEEP
sqlsql-server-2005concatenation
Jmh2013
Jmh2013

Reputation: 2777

concat column names with string inside a string

I am trying to create a pretty column to display but I cannot get a space or hyphen to work. I want the column to display like

Jun-2014 or Jun 2014 but I get errors when I try to insert it the normal way due to the query already being inside a string.

Here is the part I am having trouble with:

(LEFT(DATENAME(month, leadtime),3) + " " + CONVERT(varchar, DATEPART(year, leadtime))) as leadmonth

And here is the rest of the query for context:

set @query = 'SELECT name AS "Lead Source",' + @cols + '
from 
(
SELECT d.name, COUNT(u.lead_source_id) AS totalLeads, + "["+ (LEFT(DATENAME(month, leadtime),3) + " " + CONVERT(varchar, DATEPART(year, leadtime)))+"] as leadmonth" +
FROM DI_TrackingDB.dbo.userleads u
INNER JOIN GSPremiumServices.dbo.supplier_product_lead_source_def d ON d.lead_source_id = u.lead_source_id
WHERE vend_id = 355135
    AND u.lead_source_id IS NOT NULL
GROUP BY u.lead_source_id, d.name, DATENAME(month, leadtime), DATEPART(year, leadtime), Convert(varchar(7), leadtime, 126)
) x
pivot 
(
max(totalLeads)
for leadmonth in (' + @cols + ')
) p '

Please keep in mind I am setting @query so I am already inside of a string. Any suggestions are appreciated.

Upvotes: 1

Views: 2202

Answers (2)

Jmh2013
Jmh2013

Reputation: 2777

The answer was quite simple. Not sure if people didn't quite understand my initial question but the answer was to use two ' on either side of the inner string. Below is the final version of the code that works. 

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(monthyear)
    FROM (SELECT DISTINCT (LEFT(DATENAME(MONTH, month_start),3)+ '-' + CONVERT(VARCHAR, DATEPART(YEAR, month_start))) AS monthyear, month_start
          FROM dbo.mo_vendor_month WITH(NOLOCK)
          WHERE vend_id = #arguments.prospectId#
          AND month_start >= '#startDate#'
         )alertsMonths
        ORDER BY month_start ASC
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
     ,1,1,'')

    SET @query = 'SELECT ' + @cols + '
    FROM 
    (
        SELECT total_unique_leads,(LEFT(DATENAME(MONTH, month_start),3)+ ''-'' + CONVERT(VARCHAR, DATEPART(YEAR, month_start))) AS monthyear
        FROM dbo.mo_vendor_month
        WHERE vend_id = #arguments.prospectId#
        AND month_start >= ''#startDate#''
        GROUP BY month_start, total_unique_leads
        ) x
        pivot 
        (
        max(total_unique_leads)
        FOR monthyear IN (' + @cols + ')
        ) p '

EXECUTE(@query)

Upvotes: 2

SlimsGhost
SlimsGhost

Reputation: 2909

Just put square brackets around the field name you're constructing, like this:

"[" + (LEFT(DATENAME(month, leadtime),3) + " " + CONVERT(varchar, DATEPART(year, leadtime))) + "] as leadmonth"

I would also be ready to handle any single quotes or double quotes that might occur (by escaping them as needed when building your string). Other than that, enclosing a field name in square brackets allows you to use characters that normally would not be allowed in an identifier's name.

Upvotes: 0

Related Questions