How to change the SQL result header to date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to change the SQL result header to date

Author

Topic

lucky7456969
Starting Member

6 Posts

Posted - 2015-04-13 : 05:06:30

I have collectively select some data based on groups of months
like


        3 months from now |  6 months from now | 9 months from now
Cat 1       3                  5                10
Cat 2       8                  2                 3
Cat 3       9                  4                15
Total      21                 11                28

I wonder how can I replace the title of 3 months now to
the specified date
Any good links would be helpful.
Thanks
Jack

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-04-13 : 06:27:55

You can achieve the output in some methods. I am giving an two ideas

1. Use dynamic SQL

2. Use PIVOT

Regards
Viggneshwar A

lucky7456969
Starting Member

6 Posts

Posted - 2015-04-14 : 02:19:41

It is very difficult to achieve, but how can I assign @retval3, 2, 1, 0
to the AS?


USE RCS;

DECLARE @DateColumnsQuery3 NVARCHAR(MAX);
DECLARE @retval3 NVARCHAR(MAX);   
DECLARE @ParamDefin3 NVARCHAR(MAX);
DECLARE @DateColumnNames3 NVARCHAR(MAX);

SET @ParamDefin3 = N'@retvalOUT3 NVARCHAR(MAX) output';
SET @DateColumnsQuery3 = N'Select @retvalOUT3 = DATEADD(month, -3, GETDATE())';                   
EXEC sp_executesql @DateColumnsQuery3, @ParamDefin3, @retvalOUT3=@retval3 OUTPUT;
 

Select @retval3;


                            
EXEC sp_executesql @DateColumnsQuery3, @ParamDefin3, @retvalOUT3=@retval3 OUTPUT;

 

DECLARE @DateColumnsQuery2 NVARCHAR(MAX);
DECLARE @retval2 NVARCHAR(MAX);   
DECLARE @ParamDefin2 NVARCHAR(MAX);
DECLARE @DateColumnNames2 NVARCHAR(MAX);

SET @ParamDefin2 = N'@retvalOUT2 NVARCHAR(MAX) output';
SET @DateColumnsQuery2 = N'Select @retvalOUT2 = DATEADD(month, -2, GETDATE())';                   
EXEC sp_executesql @DateColumnsQuery2, @ParamDefin2, @retvalOUT2=@retval2 OUTPUT;

Select @retval2;

DECLARE @DateColumnsQuery1 NVARCHAR(MAX);
DECLARE @retval1 NVARCHAR(MAX);   
DECLARE @ParamDefin1 NVARCHAR(MAX);
DECLARE @DateColumnNames1 NVARCHAR(MAX);

SET @ParamDefin1 = N'@retvalOUT1 NVARCHAR(MAX) output';
SET @DateColumnsQuery1 = N'Select @retvalOUT1 = DATEADD(month, -1, GETDATE())';                   
EXEC sp_executesql @DateColumnsQuery1, @ParamDefin1, @retvalOUT1=@retval1 OUTPUT;

Select @retval1;

DECLARE @DateColumnsQuery0 NVARCHAR(MAX);
DECLARE @retval0 NVARCHAR(MAX);   
DECLARE @ParamDefin0 NVARCHAR(MAX);
DECLARE @DateColumnNames0 NVARCHAR(MAX);

SET @ParamDefin0 = N'@retvalOUT0 NVARCHAR(MAX) output';
SET @DateColumnsQuery0 = N'Select @retvalOUT0 = DATEADD(month, -0, GETDATE())';                   
EXEC sp_executesql @DateColumnsQuery0, @ParamDefin0, @retvalOUT0=@retval0 OUTPUT;
 

Select @retval0;

 
select final.RespName, final._3Months AS @retval, final._2Months AS @retval2 ,final._1Month AS @retval1 , final._0Month @retval0 
      from

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 06:07:13

You need to use dynamic SQL:


SELECT @MySQL = 'select final.RespName, final._3Months AS ' + @retval + ', final._2Months AS ' + @retval2 
             + ',final._1Month AS ' + @retval1 + ', final._0Month ' + @retval0 + '
      from ...'

Note that you don't need to use sp_executesql to get the values for your @retval variables, e.g. replace


SET @ParamDefin3 = N'@retvalOUT3 NVARCHAR(MAX) output';
SET @DateColumnsQuery3 = N'Select @retvalOUT3 = DATEADD(month, -3, GETDATE())';                   
EXEC sp_executesql @DateColumnsQuery3, @ParamDefin3, @retvalOUT3=@retval3 OUTPUT;

with


SELECT @retval3 = DATEADD(month, -3, GETDATE()),
       @retval2 = DATEADD(month, -2, GETDATE()),
       ... etc ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources