Can You Concatenate a Variable to a Field Name in a SQL Statement

[minifelt]

Hello,

I'm trying to concatenate a variable to a sql statement that creates a pivot table. This statement is givig me an error

Select ship_from,
'Jan-'+@cyear=sum(case when invoice_dt >= '01/01/' + @cyear + ' 00:00:00:000' and invoice_dt< '01/31/' + @cyear + ' 23:59:59:999' then quantity else 0 end),
Feb=sum(case when invoice_dt >= '02/01/' + @cyear + ' 00:00:00:000' and invoice_dt< '02/29/' + @cyear + ' 23:59:59:999' then quantity else 0 end),
Mar=sum(case when invoice_dt >= '03/01/' + @cyear + ' 00:00:00:000' and invoice_dt< '03/31/' + @cyear + ' 23:59:59:999' then quantity else 0 end)

From my Table
group by ship_from

Can you give me an idea how I could make this string 'Jan-'+@cyear become the column name of my pivot table?

Thank you much.

 

[Qik3Coder]

declare @a varchar(10)
declare @SQL as varchar(400)
set @a='Stuff'
set @sql = 'select 1 as ''' + @a + ''''
select '[' + @sql +']'
exec (@sql)

-Sometimes the answer to your question is the hack that works

 

[bborissov]

Select ship_from,
       SUM(CASE WHEN MONTH(invoice_dt) = 1
                     THEN Quantity 
                     ELSE 0 END) AS Jan
....
       SUM(CASE WHEN MONTH(invoice_dt) = 12
                     THEN Quantity 
                     ELSE 0 END) AS Dec
FROM YourTable
WHERE YEAR(invoice_dt) = @cyear
group by ship_from

Why should you name your fields with year when you ask only for one year and you new which year is that?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[minifelt]

Hello Borislav, I actually need two take 24 months of data in a pivot table. This is the reason why I need to concatenate the year variable with the field name for my pivot table.

Hello Quik3Coder, please give me more information on how to make your example work. I need to have a pivot table with mmm-yyyy as column headers and defect category as rows.

Thank you.

 

[Qik3Coder]

You have everything there that you need. You asked for help. I gave you an example that did what you need it to do.

If you still can't figure it out, then at least copy and paste it into Query Analyzer and see what it does.

If you can't figure out what it is doing, then I suggest looking through the FAQ for how SQL code works.

L8r
David

-Sometimes the answer to your question is the hack that works