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

Loop through table to pass variables? Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 9:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
I'm looking for a way to run the each of the values in a table to populate a single SELECT statement (which places the results into a final table). For instance, in the below script, I would like to avoid declaring @Prog three times. I want to run through ProgTbl, pick up the three variables, and pass them into the SELECT statement. The outcome in FinalTbl would be:

Total   Prog
2 AR-1
1 AR-9
3 AR-14


Below is an example script. Would someone mind pointing me in the right direction? Thanks, as always, for the help!

--Create tblCount
CREATE TABLE tblCount
(
ID CHAR(10) NOT NULL,
Prog VARCHAR(50) NOT NULL,
)
;

INSERT INTO tblCount
(
ID,
Prog
)

VALUES
('1', 'AR-1'),
('2', 'AR-1'),
('3', 'AR-9'),
('4', 'AR-14'),
('5', 'AR-14'),
('6', 'AR-14')
;

--Create Prog
CREATE TABLE ProgTbl
(
Prog VARCHAR(50) NOT NULL
)

INSERT INTO ProgTbl
(
Prog
)

VALUES
('AR-1'),
('AR-9'),
('AR-14')
;

--Create FinalTbl
CREATE TABLE FinalTbl
(
Total NUMERIC(4,0) NOT NULL,
Prog VARCHAR(50) NOT NULL
)
;

--Table checks
select * from tblCount; select * from ProgTbl; select * from FinalTbl
;


/*At this point I would like to run through each of the values in Prog and insert them into @Prog*/
INSERT INTO FinalTbl(Total, Prog)

SELECT COUNT(ID)
FROM tblCount
WHERE Prog = @Prog

Post #1395773
Posted Wednesday, December 12, 2012 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
Like this.

SELECT COUNT(ID), Prog
FROM tblCount
GROUP BY Prog



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1395774
Posted Wednesday, December 12, 2012 9:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
Thanks for your response!

I should have made my example a little harder. My production project is much bigger, but I was trying to use the bare minimum so as not to cloud the request. I truly need to use values from a different table to pass into a single variable. So in the background a single SELECT statement would have a single variable (@Prog) and pass the three ProgTbl.Prog values to build the values in the FinalTbl. Does that make sense?
Post #1395779
Posted Wednesday, December 12, 2012 11:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 1,853, Visits: 2,011
Actually Sean's code will give you the counts you're looking for, and in a more efficient manner than trying to get each one separately using the variable. If the ProgTbl restricts the Prog values or has Prog values that don't match any record in tblCount, then maybe do it as a join?
INSERT INTO FinalTbl(Total, Prog)
SELECT COUNT(*), p.Prog
FROM ProgTbl p
INNER JOIN tblCount c ON p.Prog = c.Prog
GROUP BY p.Prog

Post #1395824
Posted Wednesday, December 12, 2012 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a

and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?
Post #1395837
Posted Wednesday, December 12, 2012 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
DataAnalyst011 (12/12/2012)
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a


All you have done here is insert whatever value is in your @prog variable for each row.


and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?


Your example is so abstract it is really hard to tell what you are doing. What about a temp table. or a cross apply to a cte?

If you can be a bit more specific in what you are doing we can come up with a way to do this.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1395842
Posted Wednesday, December 12, 2012 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
Using your description in your last post here is some code that will do exactly what you said you want to happen.

create table X
(
Prog int
)

insert X
select 1 union all
select 2 union all
select 3 union all
select 4

create table Q
(
ResultColumn int
)

insert Q
select Prog
from X

select * from Q

drop table X
drop table Q



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1395844
Posted Wednesday, December 12, 2012 12:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 1,853, Visits: 2,011
DataAnalyst011 (12/12/2012)
...In my production project, the values don't exist and I use the variables to print them into a row for a business process need...

OK, so if I'm understanding this then you still want the items from ProgTbl even if there are no matching records in tblCount? Then the join would be a LEFT OUTER JOIN like this:
INSERT INTO ProgTbl
(Prog)
VALUES
('XX-99')

INSERT INTO FinalTbl(Total, Prog)
SELECT COUNT(c.prog), p.Prog
FROM ProgTbl p
LEFT OUTER JOIN tblCount c ON p.Prog = c.Prog
GROUP BY p.Prog

Post #1395845
Posted Wednesday, December 12, 2012 12:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,616, Visits: 2,119
DataAnalyst011 (12/12/2012)
Thanks for your reply also.

I didn't do a very good job setting this up. I just tried to create a quick example to ask about a concept - Is it possible to loop through multiple variables in a single statement. In my production project, the values don't exist and I use the variables to print them into a row for a business process need.

A table called x has these values in a Prog column: 1, 2, 3, 4

What I want to do is pass each of those variables in the below statement:

insert into q (resultcolumn)
select @prog
from a

and the results in resultcolumn is:
1
2
3
4


This would keep me from declaring and setting the value for @prog 4 times (one for each of the numbers). Does that make sense?


If the maximum number of different possible values isn't large, maybe a PIVOT would work:

CREATE TABLE #X (
Prog INT
)
INSERT INTO #X VALUES (1), (2), (3), (4)

SELECT [1] AS N1, [2] AS N2, [3] AS N3, [4] AS N4
FROM #X
PIVOT (MAX(Prog) FOR Prog IN ([1], [2], [3], [4])) AS PVT

DROP TABLE #X



Steve
(aka sgmunson)

Internet ATM Machine
Post #1395851
Posted Thursday, December 13, 2012 2:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
These are helpful replies, and give me a little direction. I'm going to work on it some more. If I get stuck again, I'll trying and build exactly what's happening. Its fairly involved, but I think it would be worth it. Thanks a TON for the help.
Post #1396398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse