December 12, 2012 at 9:44 am
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
(
IDCHAR(10)NOT NULL,
ProgVARCHAR(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
(
ProgVARCHAR(50)NOT NULL
)
INSERT INTO ProgTbl
(
Prog
)
VALUES
('AR-1'),
('AR-9'),
('AR-14')
;
--Create FinalTbl
CREATE TABLE FinalTbl
(
TotalNUMERIC(4,0)NOT NULL,
ProgVARCHAR(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
December 12, 2012 at 9:46 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 9:58 am
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?
December 12, 2012 at 11:30 am
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
December 12, 2012 at 12:18 pm
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?
December 12, 2012 at 12:33 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 12:36 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 12:36 pm
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
December 12, 2012 at 12:42 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 13, 2012 at 2:16 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply