Server中的病区wise计数

Question

select distinct
    Patient_Ref_master.Dept_ID as 'dept',
    Patient_Ref_master.Male_femal as 'Gender',
    count(Patient_Ref_master.Pat_ID) as 'count'
from 
    Patient_Ref_master
left join 
    Patient_Master on Patient_Master.Pat_Code=Patient_Ref_master.Pat_ID
where 
    (Patient_Ref_master.Age > 16 
     and dbo.Patient_Master.Pat_Sex = 2 
     and Patient_Ref_master.creation_Date = '2013/08/02') 
    or 
    (Patient_Ref_master.Age > 16 
     and dbo.Patient_Master.Pat_Sex = 1 
     and Patient_Ref_master.creation_Date = '2013/08/02') 
    or
    (Patient_Ref_master.Age >= 0 
     and Patient_Ref_master.Age <= 16 
     and dbo.Patient_Master.Pat_Sex = 2 
     and Patient_Ref_master.creation_Date = '2013/08/02') 
    or
    (Patient_Ref_master.Age >= 0 
     and Patient_Ref_master.Age <= 16 
     and dbo.Patient_Master.Pat_Sex = 1 
     and Patient_Ref_master.creation_Date = '2013/08/02') 
group by 
    Patient_Ref_master.Male_femal, Patient_Ref_master.Dept_ID

上面是我的查询，它返回表，如下所示

  Dept    Gender           Count
    102    Females   3
    102    Males     4
    103    Boys          2
    103    Females   2
    103    Girls     1
    103    Males     1
    104    Females   6
    104    Males     1

这里我正在根据部门统计男性，女性，女孩和男孩的数量。但我希望以下列方式显示输出

 Dept    Males Females Boys Girls 
    102    3        2     5     5  
    103    4        5     2     6   
    104    2        1     1     5

这是部门统计的男孩，女孩，男性和女性。我要做什么才能得到类似于上述模式的东西？Pivot是可以选择的吗？我从未使用过Pivot。

请帮帮忙。谢谢

Answer 1

;WITH MyCTE AS
(
    SELECT    DISTINCT
              Patient_Ref_master.Dept_ID AS 'dept',
              Patient_Ref_master.Male_femal AS 'Gender',
              COUNT(Patient_Ref_master.Pat_ID) AS 'count'
    FROM      Patient_Ref_master
              LEFT JOIN Patient_Master 
                  ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
    WHERE    (
                  Patient_Ref_master.Age > 16 
                  AND dbo.Patient_Master.Pat_Sex = 2 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age > 16 
                  AND dbo.Patient_Master.Pat_Sex = 1 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age >= 0 
                  AND Patient_Ref_master.Age <= 16 
                  AND dbo.Patient_Master.Pat_Sex = 2 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age >= 0 
                  AND Patient_Ref_master.Age <= 16 
                  AND dbo.Patient_Master.Pat_Sex = 1 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
    GROUP BY Patient_Ref_master.Male_femal,
             Patient_Ref_master.Dept_ID
)
SELECT     Dept,
           [Males], 
           [Females], 
           [Boys], 
           [Girls]
FROM       (
               SELECT Gender, 
                      Count ,
                      Dept
               FROM   MyCTE
           ) AS SourceTable
PIVOT
(
    MAX(Count)
    FOR Gender IN ([Males], [Females], [Boys], [Girls])
) AS PivotTable

和MyCTE一样..。块定义公共表表达式。这是一种定义某种事物的方法，就像飞行中的视图。在本例中，我使用它是为了使代码更具可读性。您可以找到更多关于使用公共表表达式的信息。

然后，PIVOT代码是将表行转换为列的一种方法，这实际上就是您所要求的。您可以省略CTE部分，并且可以在透视的FROM子句中添加代码，但它可能有些不可读。关于使用枢轴和UNPIVOT枢轴的更多信息

不幸的是，枢轴的工作方式只能在其中定义三列。

1. 将成为结果表的"ID“的
2. 包含要表示为行的值的
3. 最后一个包含您需要的聚合的

所以枢轴就是这样，没有别的东西。但是枢轴仍然是一张桌子，你可以把它和其他表格连接起来。

另外，我还必须注意，您可以定义多个CTE，每个CTE都包含前面定义的引用。语法是：

;WITH CTE1 AS
(
  CTE1 SELECT
)
, CTE2
(
  CTE2 SELECT ... May refer to CTE1
)

因此，一个完整的答案应该是：

;WITH MyCTE AS
(
    SELECT    DISTINCT
              Patient_Ref_master.Dept_ID AS 'dept',
              Patient_Ref_master.Male_femal AS 'Gender',
              COUNT(Patient_Ref_master.Pat_ID) AS 'count'
    FROM      Patient_Ref_master
              LEFT JOIN Patient_Master 
                  ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
    WHERE    (
                  Patient_Ref_master.Age > 16 
                  AND dbo.Patient_Master.Pat_Sex = 2 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age > 16 
                  AND dbo.Patient_Master.Pat_Sex = 1 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age >= 0 
                  AND Patient_Ref_master.Age <= 16 
                  AND dbo.Patient_Master.Pat_Sex = 2 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
             OR
             (
                  Patient_Ref_master.Age >= 0 
                  AND Patient_Ref_master.Age <= 16 
                  AND dbo.Patient_Master.Pat_Sex = 1 
                  AND Patient_Ref_master.creation_Date = '2013/08/02'
             )
    GROUP BY Patient_Ref_master.Male_femal,
             Patient_Ref_master.Dept_ID
)
,PivotCTE AS
(
    SELECT     Dept,
               [Males], 
               [Females], 
               [Boys], 
               [Girls]
    FROM       (
                   SELECT Gender, 
                          Count ,
                          Dept
                   FROM   MyCTE
               ) AS SourceTable
    PIVOT
    (
        MAX(Count)
        FOR Gender IN ([Males], [Females], [Boys], [Girls])
    ) AS PivotTable
)
SELECT    Dept,
          DeptName
          [Males], 
          [Females], 
          [Boys], 
          [Girls]
FROM      PivotCTE P
          JOIN DepartmentTable D
               ON P.Dept = D.Dept

Answer 2

是的，PIVOT是解决方案

select *
from 
     ( 
        -- your query here
     ) d
    pivot (sum([count]) for gender in (females,males,boys,girls)) p

或者更好

select *
from 
     ( 
         select 
              Patient_Ref_master.Dept_ID,
              Patient_Ref_master.Male_femal,      
         from Patient_Ref_master
         left join Patient_Master on Patient_Master.Pat_Code=Patient_Ref_master.Pat_ID
         where 
             -- your filter here
     ) d
    pivot (count(pat_code) for Male_femal in (females,males,boys,girls)) p

请参阅http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx