CODEWITHSUNDEEP
sqlsql-server
user3312649
user3312649

Reputation: 300

SQL stuff and coalesce

hello I just want to know something.. Say I have this following query..

DECLARE @earninglist varchar(1000)
       Set @earninglist=STUFF((SELECT DISTINCT '],['
            +LTRIM([Description]) FROM
            PR_Earnings 
            ORDER BY '],[' + LTRIM([Description])
                        FOR XML PATH('')
                        ), 1, 2, '')+']'

Declare @sql varchar(max)
set @sql='Select '+@earninglist+' from earnings;'
exec(@sql);

say that earning list contains this fields: 'Cola','Incentives' How can I declare @earninglist so that I can get this following query:

set @sql='Select Coalesce([Cola],0)Cola,Coalesce([Incentives],0)Incentives from earnings'

using stuff.. please help..

hmm.. if you notice that @earninglist in the first set @sql .. it will turn out to be like this: 'Select [Cola],[Incentives] from earnings' what i want to do is to make the result set of @earninglist to be like this:'Select Coalesce([Cola],0)Cola,Coalesce([Incentives],0)Incentives from earnings' .. is it possible??

Upvotes: 0

Views: 2936

Answers (1)

Deepshikha
Deepshikha

Reputation: 10284

Try as:

DECLARE @earninglist varchar(1000)
       Set @earninglist=STUFF((SELECT DISTINCT '],Coalesce([' +LTRIM([Description])+'],0)['+LTRIM([Description]) FROM
            PR_Earnings 
            ORDER BY '],Coalesce([' +LTRIM([Description])+'],0)['+LTRIM([Description])
                        FOR XML PATH('')
                        ), 1, 2, '')+']'

Declare @sql varchar(max)
set @sql='Select '+@earninglist+' from earnings;'
exec (@sql);

Upvotes: 1

Related Questions