Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL)


Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL)

Author
Message
vsts.dev
vsts.dev
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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??
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45443 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45443 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search