January 28, 2010 at 12:40 am
Hi All,
I have a SQL script below
DECLARE @sumCol as varchar(500),
@grpColQ as varchar(500)
SET @grpColQ = 'det.PlcForecastID,' + @colList
SET @sumCol = REPLACE(@colList,',','+')
--result @colList = [Q208],[Q308],[Q408],[Q109],[Q209],[Q309],[Q409],[Q110],[Q210],[Q310],[Q410],[Q111],[Q211],[Q311],[Q411],[Q112]
--result @sumCol = [Q208]+[Q308]+[Q408]+[Q109]+[Q209]+[Q309]+[Q409]+[Q110]+[Q210]+[Q310]+[Q410]+[Q111]+[Q211]+[Q311]+[Q411]+[Q112]
SELECT det.*, ,SUM(@sumCol)
FROM #plcDet det
GROUP BY @grpColQ
My problem is:
(1) once i pass the @ parameter inside the SELECT statement, it cant convert the string to the query statement for the SUM()
(2) same at here, the GROUP BY there....
How should I change the String to the Query method?
🙂 thx for help!
January 28, 2010 at 2:26 am
My wild guess is that you are looking for a dynamic SQL. This should get you started,
CREATE TABLE #TEST(FID int, Q01 int, Q02 int)
INSERT INTO #TEST VALUES (1, 1, 1)
INSERT INTO #TEST VALUES (1, 2, 2)
INSERT INTO #TEST VALUES (2, 1, 1)
INSERT INTO #TEST VALUES (2, 2, 2)
INSERT INTO #TEST VALUES (3, 2, 2)
DECLARE @sumCol as nvarchar(500),
@grpColQ as nvarchar(500),
@colList as nvarchar(500) ,
@sql as nvarchar(max)
SET @grpColQ = 'FID'
SET @colList = 'Q01,Q02'
SET @sumCol = REPLACE(@colList,',','+')
SET @sql = 'SELECT FID,SUM(' + @sumCol + ') FROM #test det GROUP BY ' + @grpColQ
PRINT @sql
EXEC Sp_executeSQL @sql
---------------------------------------------------------------------------------
January 28, 2010 at 7:05 am
is that have any others method?
eg: convert or cast?
because i involve many changes ....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply