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

--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

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

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

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

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

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