我们可以通过为约会选择不同的记录来实现这一点（将除“a000”和“a999”之外的所有管理代码视为约会）。

SELECT DISTINCT t.Id, '1' AS 'Had_appointment', t.[date], datename(month, [date]) [Month], year([date]) [Year], t.Clinic
FROM @t t   
WHERE Admin_codes NOT IN ('A000', 'A999')

请看这里的演示。

我假设你说的“表”是指查询/结果集。除了@sacse的答案，你还可以通过groupby完成同样的任务。

DECLARE @Data TABLE ( id INT, Admin_codes VARCHAR(4), [Date] DATE, Clinic VARCHAR(1) );
INSERT INTO @Data ( id, Admin_codes, [Date], Clinic ) VALUES
    ( 1, 'AAA2', '2019-01-03', 'A' ),( 1, 'D22S', '2019-01-03', 'A' ),( 1, 'FFD3', '2019-01-05', 'B' ),
    ( 1, 'E222', '2019-01-05', 'B' ),( 5, 'EEE1', '2019-04-03', 'C' ),( 5, 'P332', '2019-04-03', 'C' ),
    ( 5, 'AA33', '2019-04-03', 'C' ),( 5, 'XC22', '2019-04-03', 'C' ),( 6, 'A000', '2019-02-19', 'C' ),
    ( 7, 'A999', '2019-03-11', 'C' );

SELECT
    id,
    1  AS Had_appointment,
    [Date], 
    DATENAME ( month, [Date] ) AS [Month], 
    YEAR ( [Date] ) AS [Year], 
    Clinic
FROM @Data
WHERE
    Admin_codes NOT IN ( 'A000', 'A999' )
GROUP BY
    id, [Date], Clinic
ORDER BY
    id, [Date];

退货

+----+-----------------+------------+---------+------+--------+
| id | Had_appointment |    Date    |  Month  | Year | Clinic |
+----+-----------------+------------+---------+------+--------+
|  1 |               1 | 2019-01-03 | January | 2019 | A      |
|  1 |               1 | 2019-01-05 | January | 2019 | B      |
|  5 |               1 | 2019-04-03 | April   | 2019 | C      |
+----+-----------------+------------+---------+------+--------+

如果您计划进行任何额外的聚合（例如sum、avg等），使用groupby将非常有用。