April 5, 2013 at 5:06 am
Create procedure temp
(
@MID smallint
)
as
Begin
select TranID,
[MonthValue]=(CASE WHEN @MID=1 THEN Jan
WHEN @MID=2 THEN Feb
WHEN @MID=3 THEN Mar
WHEN @MID=4 THEN Apr
WHEN @MID=5 THEN May
WHEN @MID=6 THEN Jun
WHEN @MID=7 THEN Jul
END)
FROM
TblTran as M
where TranID=1 and
M.Month = @MID
end
This is a stored procedure with a parameter @MID that i'm using to generate a report using SSRS.
If a single value is passed to the parameter it works fine.
For example-
Transaction Table
TranID | Apr | May | Jun | Jul
1 | 50 | 30 | 11 | 30
2 | 51 | 39 | 100 | 30
if i execute with
Exec 4
the result is what i expect
TranID | MonthValue
1 | 50 **-- ie Aprils value**
But I need to pass multiple values to the parameter
like
exec 4,5,6
and desired result should be
TranID | MonthValue
1 | 50,30,11 ***-->Comma Separated values of columns
how can i acheive result like this??
April 8, 2013 at 12:54 pm
Hi vsts dev,
SQL is a relational database. With that said, you should try to work with sets of data, not strings.
The solution below uses table value parameter to a stored procedure. This could be a function if you wanted.
The trick is to set the input table with three rows (4, 5, 6) for apr, may, and jun.
The stored procedure creates a dynamic SQL statement using the PIVOT command to make rows columns.
(3 row(s) affected)
the_trans_id apr may jun
------------ ----------- ----------- -----------
1 50 30 11
2 51 39 100
The result is a relational set (table).
If you wanted to do other things like filter by trans_id, convert it to a table value function.
Good luck.
Sincerely
J
--
-- Sample code
--
-- Assumes you have a test database
use test;
go
-- Temp table - transactions
create table trans
(
the_trans_id int,
the_month_id int,
the_value int,
)
-- Insert data - transactions
insert into trans values
(1, 4, 50),
(1, 5, 30),
(1, 6, 11),
(1, 7, 30),
(2, 4, 51),
(2, 5, 39),
(2, 6, 100),
(2, 7, 30);
-- Show data - transactions
select * from trans;
-- Create table input type
create type my_table_input as table
(
the_month_id int
);
go
-- Create stored procedure with (table value parameter)
alter procedure dbo.usp_data_by_month_list
--create procedure dbo.usp_data_by_month_list
@var_tvp my_table_input READONLY
as
-- Show not count
set nocount on;
-- local variables
declare @var_cnt int;
declare @var_stmt nvarchar(max) = '';
declare @var_months1 varchar(max);
declare @var_months2 varchar(max);
declare @var_months3 varchar(3);
-- set to defaults
select @var_cnt = 1;
select @var_stmt = '';
select @var_months1 = '';
select @var_months2 = '';
select @var_months3 = '';
-- make up list
while (@var_cnt < 13)
begin
-- get a column alias
select @var_months3 = '';
if exists (select * from @var_tvp v where v.the_month_id = @var_cnt)
begin
select @var_months3 =
(
case
when @var_cnt = 1 then 'jan'
when @var_cnt = 2 then 'feb'
when @var_cnt = 3 then 'mar'
when @var_cnt = 4 then 'apr'
when @var_cnt = 5 then 'may'
when @var_cnt = 6 then 'jun'
when @var_cnt = 7 then 'jul'
when @var_cnt = 8 then 'aug'
when @var_cnt = 9 then 'sep'
when @var_cnt = 10 then 'oct'
when @var_cnt = 11 then 'nov'
when @var_cnt = 12 then 'dec'
else 'unk'
end
)
end
-- just values
if exists (select * from @var_tvp v where v.the_month_id = @var_cnt)
select @var_months1 = @var_months1 + '[' + replace(convert(varchar(2), @var_cnt), ' ', '') + '] as [' + @var_months3 + '], ';
-- rename to months
if exists (select * from @var_tvp v where v.the_month_id = @var_cnt)
select @var_months2 = @var_months2 + '[' + replace(convert(varchar(2), @var_cnt), ' ', '') + '], ';
-- increment counter
select @var_cnt = @var_cnt + 1;
end
-- remove last comma - 1
if (len(@var_months1) > 2)
select @var_months1 = left(@var_months1, len(@var_months1) - 1);
-- remove last comma - 2
if (len(@var_months2) > 2)
select @var_months2 = left(@var_months2, len(@var_months2) - 1);
-- create dynamic pivot stmt
select @var_stmt = @var_stmt + 'select [the_trans_id], ' + @var_months1 + ' FROM ';
select @var_stmt = @var_stmt + '(SELECT [the_trans_id], [the_month_id], [the_value] from [trans]) AS [SourceTable] ';
select @var_stmt = @var_stmt + 'PIVOT ( ';
select @var_stmt = @var_stmt + 'avg([the_value]) ';
select @var_stmt = @var_stmt + 'FOR [the_month_id] IN ( ' + @var_months2 + ') ';
select @var_stmt = @var_stmt + ') AS [PivotTable]';
-- debugging line
--print @var_stmt;
-- execute
execute sp_executesql @var_stmt;
go
-- Call the stored procedure
declare @var_local my_table_input;
insert into @var_local values (4), (5), (6);
execute dbo.usp_data_by_month_list @var_local;
John Miner
Crafty DBA
www.craftydba.com
April 9, 2013 at 8:53 am
If you can't use a table valued parameter you can pass a list of values and then create a "splitter" function that turns that list into a set of data that you can then JOIN on. You should check out this article[/url].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2013 at 2:18 am
vsts.dev (4/5/2013)
But I need to pass multiple values to the parameterlike
exec 4,5,6
and desired result should be
TranID | MonthValue
1 | 50,30,11 ***-->;Comma Separated values of columns
how can i acheive result like this??
I noticed that the above part of the OPs question was never answered. As Jack Corbett suggested, you just need a good splitter and the code becomes simple.
Here's the test data I used.
DROP TABLE dbo.TblTran
SELECT TranID, Month, MonthValue
INTO dbo.TblTran
FROM (
SELECT 1, 4, 50 UNION ALL
SELECT 1, 5, 30 UNION ALL
SELECT 1, 6, 11 UNION ALL
SELECT 1, 7, 30 UNION ALL
SELECT 2, 4, 51 UNION ALL
SELECT 2, 5, 39 UNION ALL
SELECT 2, 6, 100 UNION ALL
SELECT 2, 7, 30
) d (TranID, Month, MonthValue)
;
Stored procedures are a real PITA if you want to use their data for something else so I made this as an iTVF (Inline Table Valued Function).
CREATE FUNCTION dbo.SomeFunctionName
(@MonthsCSV VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteGetData AS
(
SELECT t.TranID
, t.Month
, t.MonthValue
FROM dbo.TblTran t
JOIN dbo.DelimitedSplit8K(@MonthsCSV,',') split
ON t.Month = split.Item
)
SELECT c1.TranID
, MonthValues =
STUFF(
(
SELECT ',' + CAST(MonthValue AS VARCHAR(10))
FROM cteGetData c2
WHERE c2.TranID = c1.TranID
ORDER BY c2.[Month]
FOR XML PATH('')
)
,1,1,'')
FROM cteGetData c1
GROUP BY c1.TranID
;
Then you can call it like the following:
SELECT * FROM dbo.SomeFunctionName('4,5,6');
That returns the desired answer:
TranID MonthValues
----------- ------------
1 50,30,11
2 51,39,100
As a bit of a sidebar, please consider not abbreviating names nor using reserved words like "Month" for names of objects or columns. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2013 at 2:24 am
j.miner (4/8/2013)
select @var_months3 =(
case
when @var_cnt = 1 then 'jan'
when @var_cnt = 2 then 'feb'
when @var_cnt = 3 then 'mar'
when @var_cnt = 4 then 'apr'
when @var_cnt = 5 then 'may'
when @var_cnt = 6 then 'jun'
when @var_cnt = 7 then 'jul'
when @var_cnt = 8 then 'aug'
when @var_cnt = 9 then 'sep'
when @var_cnt = 10 then 'oct'
when @var_cnt = 11 then 'nov'
when @var_cnt = 12 then 'dec'
else 'unk'
end
)
Just as a suggestion... please consider the following which takes a whole lot less typing and is twice as fast when running.
SELECT @var_months3 = CONVERT(CHAR(3),DATEADD(mm,@var_cnt-1,0),100);
For proof of the performance pudding, please see the following article.
http://www.sqlservercentral.com/articles/formatting/72066/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2013 at 3:35 pm
Hi Jeff,
Good suggestion on using the date add + convert instead of a case statement.
Thanks for the advice.
John
John Miner
Crafty DBA
www.craftydba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply