How to store multiple values in single variable in SQL

Question

0.00/5 (No votes)

See more:

i want to store multiple values in one variable like array or list in sql.how to do that.

What I have tried:

SQL

SET @SYSRECORD=Sales A/c,Bank Accounts,Bank O/D Account,Cash-in-hand,Duties & Taxes,Expenses (Direct),Expenses (Indirect)

it is not working

i have tried this also

SQL

INSERT INTO @ListofIDs ('a'),('b'),('b'),('c'),('d'),('d');
SELECT IDs FROM @ListofIDs  AS names

Posted 29-Mar-16 1:25am

Member 12385326

Updated 30-Mar-16 20:32pm

Patrice T

v2

Add a Solution

Comments

jaket-cp 30-Mar-16 4:44am

Can you give an example on how you would like to use the list.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-03-29T01:39:00

Don't.
Instead, have a separate table which uses a foreign key back to the original, and contains the value you want to select:
SalesStaff

ID    Name
1     Joe
2     Mike

Sales

Id    SalesId   Value
1     1         100
2     1         200
3     2         300

And then use and SQL Join to "tie them together"

SQL

SELECT ss.ID, ss.Name, s.Value FROM Sales s
JOIN SalesStaff ss
ON ss.ID = s.SalesID

That gives you:

1     Joe       100
1     Joe       200
2     Mike      300

While it is possible to store multiple values in a single column but converting them to strings and comma separating them, it's a PITA to work with and always gives problems. For example, what happens if you want to delete the "500" from "1,2,330,500,5000,200,5"? It's a real pain!

Tomas Takac · Answer 2 · 2016-03-29T01:37:00

Solution 1

You are looking for table variables[^].

SQL

DECLARE @ListofIDs TABLE (id varchar(10))

INSERT INTO @ListofIDs ( id )
VALUES  ('a'),('b'),('b'),('c'),('d'),('d');

SELECT id AS [name] FROM @ListofIDs

Posted 29-Mar-16 1:37am

Tomas Takac

Comments

aarif moh shaikh 29-Mar-16 7:50am

+5

Member 12385326 · Answer 3 · 2016-03-30T20:31:00

Solution 4

i got my solution.
i used this code which creates temporary table to store multiple value.when we run our code it displays that table.

DECLARE @data TABLE (grp VARCHAR(100))

INSERT INTO @data --values('Capital Account','Loan')
SELECT 'Capital Account'
UNION ALL
SELECT 'Current Liabilities'
UNION ALL
SELECT 'Current Assets'
UNION ALL
SELECT 'Fixed Assets'
UNION ALL
SELECT 'Profit & Loss'
UNION ALL
SELECT 'Revenue Account'
UNION ALL
SELECT 'Suspense Account'
UNION ALL
SELECT 'Loan';
SELECT grp FROM @data;

Posted 30-Mar-16 20:31pm

Member 12385326

Comments

jaket-cp 31-Mar-16 4:12am

If that is all you need to do, then you do not need to insert into a temp variable table. Unless you are using the @data TABLE in multiple places.

Just do the select with union all and give the first column an alias of grp.

SELECT 'Capital Account' grp
UNION ALL
...

Member 12385326 31-Mar-16 4:20am

i am not getting it..can you show it with my example?

jaket-cp 31-Mar-16 4:23am

As in your sql code.
Remove delcare, insert and select from @data table.
Add in grp for the select statement, like below:


SELECT 'Capital Account' grp
UNION ALL
SELECT 'Current Liabilities'
UNION ALL
SELECT 'Current Assets'
UNION ALL
SELECT 'Fixed Assets'
UNION ALL
SELECT 'Profit & Loss'
UNION ALL
SELECT 'Revenue Account'
UNION ALL
SELECT 'Suspense Account'
UNION ALL
SELECT 'Loan';

Member 12385326 31-Mar-16 4:30am

but i am using this data like this
IF @abc='insert' and @Chbox_Val='p' and @Group_Name IN (SELECT grp FROM @data)

begin
insert into tbl_AccountGroup(Group_Name,Chbox_Val ,UnderGroup,Comments,flag )
values(@Group_name,@Chbox_Val,@UnderGroup,@Comments,'1')
set @Group_id= SCOPE_IDENTITY();
print 'block1'
end

for this i need to store this values in list kind of thing..so used this table.am i going write or wrong?

jaket-cp 31-Mar-16 4:37am

if that is what you are doing, then you could also do it like this:

@Group_Name IN ('Capital Account','Current Liabilities','Current Assets','Fixed Assets','Profit & Loss','Revenue Account','Suspense Account','Loan')

OR

@Group_Name IN (
	SELECT 'Capital Account'
	UNION ALL
	SELECT 'Current Liabilities'
	UNION ALL
	SELECT 'Current Assets'
	UNION ALL
	SELECT 'Fixed Assets'
	UNION ALL
	SELECT 'Profit & Loss'
	UNION ALL
	SELECT 'Revenue Account'
	UNION ALL
	SELECT 'Suspense Account'
	UNION ALL
	SELECT 'Loan'
)

But I suppose it can be maintained more easily when placed stored in the @data table.

Hope that helps you out.

But on second thought, it would be better if you create a permanent Group lookup type table. As suggested in solution 1 oops meant solution 2.

For example if a new Group_Name was required, then all you need to do is insert it into the Group lookup table rather than modifying the sql code to add the extra group into the list.

Member 12385326 31-Mar-16 5:21am

i am checking that grp name multiple times with different if elseif statements.thats why i created that permanent lookup table.

How to store multiple values in single variable in SQL

3 solutions

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us