SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamically create field list from SP results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/21/2013 :  11:27:50  Show Profile  Reply with Quote
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 DATE
DECLARE @EndTime DATE
SET @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 a

PRINT @queryString;


This is supposed to return the list as something like:
2/2013,3/2013

It 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 a
PRINT @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 DATE
DECLARE @EndTime DATE
SET @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.

Edited by - KilpAr on 03/21/2013 11:44:31

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  02:00:47  Show Profile  Reply with Quote
why do you need to use procedure to create listing of months. That can very easily be done inline

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/22/2013 :  09:15:57  Show Profile  Reply with Quote
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]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateListOfMonths](
	@StartTime date = null,
	@EndTime date = null
)
AS
BEGIN
IF @StartTime is null SET @StartTime = GETDATE();  -- Default to today
IF @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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/22/2013 :  09:44:27  Show Profile  Reply with Quote

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateListOfMonths](
	@StartTime date = null,
	@EndTime date = null
)
AS
BEGIN
DECLARE @Result VARCHAR(max) =''
IF @StartTime is null SET @StartTime = GETDATE();  -- Default to today
IF @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]
END

EXEC [CreateListOfMonths] '2012-01-01', '2012-04-01'
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/22/2013 :  11:21:32  Show Profile  Reply with Quote
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.

Edited by - KilpAr on 03/22/2013 11:22:02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  13:24:21  Show Profile  Reply with Quote
Again my question is why cant you do this in main procedure itself inline?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/22/2013 :  13:43:27  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Again my question is why cant you do this in main procedure itself inline?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Because I have no idea how to use input parameters without using a stored procedure.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  14:09:21  Show Profile  Reply with Quote
nope...you can pass the start end dates and generate it inside your main proc itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/22/2013 :  17:15:54  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

nope...you can pass the start end dates and generate it inside your main proc itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/25/2013 :  01:29:08  Show Profile  Reply with Quote
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateListOfMonths](
	@StartTime date = null,
	@EndTime date = null,
	@P_Result VARCHAR(MAX) OUTPUT
)
AS
BEGIN
	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, '') 
END
GO

DECLARE @P_output VARCHAR(MAX) =''
EXEC [CreateListOfMonths] '2012-01-01', '2012-04-01', @P_output OUTPUT
SELECT @P_output


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000