0

I need to dynamically create select columns within a query. I know I am doing it wrong but have no idea what the best way to do this is.

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

BEGIN
SELECT NAME AS  FACILITY ,
SUM(GROU1+GROUP2) AS FAC_COUNT,
--------------------ADD LOOOP HERE TO CREATE SELECT COLUMNS ?---
(SELECT COUNT(*) FROM PROGRAMS WHERE FACILITY_ID = FACILITY.ID AND PROGRAM_ID = @PROGRAM_ID
) AS PROGRAM_NAME   

----------------END LOOP?-------

FROM FACILITY
WHERE SUBTYPE IN (3,4,5)

the end result will look like this: enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James Johnson
  • 69
  • 6
  • 1
    What are the questions that the query is supposed to answer? And what are the schemas of your tables? – Eric Brandt Feb 28 '20 at 17:11
  • The query is to show the number of participants in a program for each facility. the mains tables are a facility table which holds the facilities listed, a programs table that holds the programs and a participant table that holds the program participants – James Johnson Feb 28 '20 at 17:18

1 Answers1

0

You could do a PIVOT statement, but it can also be done with conditional aggregation using CASE statements.

SELECT fac.NAME AS FACILITY
     , SUM(fac.GROUP1 + fac.GROUP2) AS FAC_COUNT
     , SUM(CASE WHEN PROGRAM_ID = 1 THEN 1 ELSE 0 END) AS [PROGRAM 1]
     , SUM(CASE WHEN PROGRAM_ID = 2 THEN 1 ELSE 0 END) AS [PROGRAM 2]
     , SUM(CASE WHEN PROGRAM_ID = 4 THEN 1 ELSE 0 END) AS [PROGRAM 4]
FROM FACILITY fac
INNER JOIN PROGRAMS pgms ON fac.ID = pgms.FACILITY_ID
WHERE fac.SUBTYPE IN (3,4,5)

Clearly, this isn't going to flex if you have Programs other than 1, 2, or 4 without having to manually change the code. To accommodate that you should look at a dynamic PIVOT query discussed here.

Isaac
  • 3,240
  • 2
  • 24
  • 31