user3598297 Japs user3598297 Japs - 2 months ago 11
SQL Question

Use @variable in COLUMN name in SQL SERVER 2012

Here, I've used SUM function on TRx1 column.
I've TRx2,3,4,..53 Columns in the table.
I want to display all the columns using loop.

DECLARE @flag INT;
SET @flag = 1;

WHILE @flag <= 2
BEGIN

select TOP 5 sd.OutletBPID, md.Product, SUM(TRx1)
from sdata as sd, md, oa
where sd.PSetID = md.PsetID
group by sd.OutletBPID, md.Product;

SET @flag = @flag + 1;
END;


The following code i did but it shows error: "Invalid Column Name"

DECLARE @flag INT;
SET @flag = 1;

WHILE @flag <= 2
BEGIN

select TOP 5 sd.OutletBPID, md.Product, SUM(TRx@flag)
from sdata as sd, md, oa
where sd.PSetID = md.PsetID
group by sd.OutletBPID, md.Product;

SET @flag = @flag + 1;
END;

Answer

You need dynamic sql

DECLARE @flag INT;
SET @flag = 1;

WHILE @flag <= 2
BEGIN

    exec ('select TOP 5 sd.OutletBPID, md.Product, SUM(TRx'+cast(@flag as varchar(20))+')
     from sdata as sd, md, oa
     where sd.PSetID = md.PsetID
     group by sd.OutletBPID, md.Product')

SET @flag = @flag + 1;
END;