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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamically create field list from SP results

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 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.

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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]
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 09:44:27
[code]
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'[/code]
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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
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 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
   

- Advertisement -