sql-server - 是否可以运行一个while循环来创建动态选择列

Question

我需要在查询中动态创建选择列。我知道我做错了，但不知道最好的方法是什么。

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)

最终结果将如下所示：

标签： sql-servertsql

Answer 1

您可以执行 PIVOT 语句，但也可以使用 CASE 语句通过条件聚合来完成。

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)

显然，如果您有除 1、2 或 4 以外的程序而无需手动更改代码，这将不会灵活。为了适应这种情况，您应该查看此处讨论的动态 PIVOT 查询。