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.
Author |
Topic |
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-21 : 11:27:50
|
I need to create a pivot table that has a list of months as the columns. I have a stored procedure that has the logic for retrieving the months. How do I parse the resultset into column names?I have tried like this:DECLARE @queryString NVARCHAR(max)DECLARE @StartTime DATEDECLARE @EndTime DATESET @StartTime = '2013-02-01';SET @EndTime = GETDATE();SELECT @querystring = COALESCE(@queryString + ',', '') + [a].[Column]FROM (SELECT [Month/Year] AS [Column] FROM EXEC [MyDatabase].[dbo].[CreateListOfMonths] @StartTime, @EndTime) AS aPRINT @queryString; This is supposed to return the list as something like:2/2013,3/2013It does work when I run it like this:DECLARE @queryString NVARCHAR(max)SELECT @querystring = COALESCE(@queryString + ',', '') + [a].[Column]FROM (SELECT [Month/Year] AS [Column] FROM [TemporaryWorkDB].[dbo].[monthlist]) AS aPRINT @queryString; where [TemporaryWorkDB].[dbo].[monthlist] is a table I created by running that [MyDatabase].[dbo].[CreateListOfMonths] @StartTime, @EndTime in forehand. Therefore, I don't need help with that actual monthlister (or with the pivot for that matter) right now, only with the way to run this query.edit. I could simplify this into:DECLARE @queryString NVARCHAR(max)DECLARE @StartTime DATEDECLARE @EndTime DATESET @StartTime = '2013-02-01';SET @EndTime = GETDATE();SELECT * INTO #MyTable FROM (EXEC [MyDatabase].[dbo].[CreateListOfMonths] @StartTime, @EndTime) A and work from there, but I still don't get it to work. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-22 : 02:00:47
|
why do you need to use procedure to create listing of months. That can very easily be done inline------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-22 : 09:15:57
|
quote: Originally posted by visakh16 why do you need to use procedure to create listing of months. That can very easily be done inline
Please, could you show me how?Requirements: User has to be able to set both dates, start and end. The result has to be one string i.e. the result set separated by commas.Example: Input: 2012-01-01, 2012-04-01. Output: 01/2012,02/2012,03/2012,04/2012 (the format doesn't matter)Here's a start that generates the table of months (which, like said, should be one string, not a table):USE [MyDatabase]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CreateListOfMonths]( @StartTime date = null, @EndTime date = null)ASBEGINIF @StartTime is null SET @StartTime = GETDATE(); -- Default to todayIF @EndTime is null SET @EndTime = GETDATE(); -- Default to today ;with MonthlyCalendar AS ( SELECT cast(@StartTime AS datetime) AS dt Union ALL SELECT dateadd(mm, 1, dt) FROM MonthlyCalendar WHERE dateadd(mm, 1, dt) <= @EndTime ) SELECT convert(varchar(12),convert(varchar(2),DATEPART(MONTH, dt)) + '/' + convert(varchar(4),DATEPART(YEAR, dt))) AS [Month/Year] FROM MonthlyCalendar WHERE dt BETWEEN @StartTime AND @EndTime OPTION (maxrecursion 0) END |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-22 : 09:44:27
|
[code]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CreateListOfMonths]( @StartTime date = null, @EndTime date = null)ASBEGINDECLARE @Result VARCHAR(max) =''IF @StartTime is null SET @StartTime = GETDATE(); -- Default to todayIF @EndTime is null SET @EndTime = GETDATE(); -- Default to today ;with MonthlyCalendar AS ( SELECT cast(@StartTime AS datetime) AS dt Union ALL SELECT dateadd(mm, 1, dt) FROM MonthlyCalendar WHERE dateadd(mm, 1, dt) <= @EndTime ) SELECT @Result = @result + ', ' + convert(varchar(12),convert(varchar(2),DATEPART(MONTH, dt)) + '/' + convert(varchar(4),DATEPART(YEAR, dt))) FROM MonthlyCalendar WHERE dt BETWEEN @StartTime AND @EndTime OPTION (maxrecursion 0) SELECT STUFF(@Result, 1, 2, '') As [Month/Year]ENDEXEC [CreateListOfMonths] '2012-01-01', '2012-04-01'[/code] |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-22 : 11:21:32
|
Thanks! I got it to work with your help, bandi. Had to research a bit, but using it like this:SET NOCOUNT ON;DECLARE @resultstring VARCHAR(max)EXEC [MyDatabase]..[MonthString] '2012-01-01', '2012-04-01', @Result=@resultstring OUTPUT;PRINT @resultstring;and modifying the procedure like this:ALTER PROCEDURE [dbo].[MonthString](@StartTime date = null, @EndTime date = NULL, @Result VARCHAR(max) OUTPUT)did it for me. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-22 : 13:24:21
|
Again my question is why cant you do this in main procedure itself inline?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-22 : 13:43:27
|
quote: Originally posted by visakh16 Again my question is why cant you do this in main procedure itself inline?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Because I have no idea how to use input parameters without using a stored procedure. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-22 : 14:09:21
|
nope...you can pass the start end dates and generate it inside your main proc itself right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-22 : 17:15:54
|
quote: Originally posted by visakh16 nope...you can pass the start end dates and generate it inside your main proc itself right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Yeah, that's possible. I just need the same code for multiple programs so I thought to use a single function to do it instead of writing the code multiple times. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 01:29:08
|
quote: Originally posted by KilpAr and modifying the procedure like this:ALTER PROCEDURE [dbo].[MonthString](@StartTime date = null, @EndTime date = NULL, @Result VARCHAR(max) OUTPUT)did it for me.
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CreateListOfMonths]( @StartTime date = null, @EndTime date = null, @P_Result VARCHAR(MAX) OUTPUT)ASBEGIN IF @StartTime is null SET @StartTime = GETDATE(); -- Default to today IF @EndTime is null SET @EndTime = GETDATE(); -- Default to today SET @P_Result = '' ;with MonthlyCalendar AS ( SELECT cast(@StartTime AS datetime) AS dt Union ALL SELECT dateadd(mm, 1, dt) FROM MonthlyCalendar WHERE dateadd(mm, 1, dt) <= @EndTime ) SELECT @P_Result = @P_Result + ', ' + convert(varchar(12),convert(varchar(2),DATEPART(MONTH, dt)) + '/' + convert(varchar(4),DATEPART(YEAR, dt))) FROM MonthlyCalendar WHERE dt BETWEEN @StartTime AND @EndTime OPTION (maxrecursion 0) SELECT @P_Result = STUFF(@P_Result, 1, 2, '') ENDGODECLARE @P_output VARCHAR(MAX) =''EXEC [CreateListOfMonths] '2012-01-01', '2012-04-01', @P_output OUTPUTSELECT @P_output --Chandu |
|
|
|
|
|
|
|