Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL) Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 5, 2013 8:54 AM
Points: 1, Visits: 7
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??
Post #1439160
Posted Monday, April 8, 2013 12:54 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:08 PM
Points: 80, Visits: 351
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
Post #1439985
Posted Tuesday, April 9, 2013 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 10,191, Visits: 13,115
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.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1440394
Posted Sunday, April 14, 2013 2:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
vsts.dev (4/5/2013)
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??


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442092
Posted Sunday, April 14, 2013 2:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442093
Posted Sunday, April 14, 2013 3:34 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:08 PM
Points: 80, Visits: 351
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
Post #1442139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse