没有聚合的SQL Server 2012 PIVOT

问题描述

我有以下示例数据:

Id    Name     Category
-----------------------
1     Joe       A
2     Joe       B
3     Joe       D
4     Mary      A
5     Mary      C
6     Mary      D

我想显示一个人所属的类别，如下所示:

I would like to show the categories a person belongs to like so:

Name   CategoryA  CategoryB  CategoryC  CategoryD
--------------------------------------------------
Joe       X          X                     X
Mary      X                     X          X

可以用1和0代替X和空格.

1's and 0's could be used in place of X's and blanks.

对我来说，这闻起来像个PIVOT问题.

This smells like a PIVOT question to me.

推荐答案

有几种方法可以转换数据.一些使用聚合函数，而另一些则不使用.但是，即使您正在旋转字符串，您仍然可以应用聚合.

There are several ways that you can transform the data. Some use an aggregate function and others don't. But even though you are pivoting a string you can still apply an aggregate.

使用CASE进行汇总:

select name,
  max(case when category = 'A' then 'X' else '' end) CategoryA,
  max(case when category = 'B' then 'X' else '' end) CategoryB,
  max(case when category = 'C' then 'X' else '' end) CategoryC,
  max(case when category = 'D' then 'X' else '' end) CategoryD
from yourtable
group by name

请参见带有演示的SQL小提琴

静态数据透视:

即使值是字符串，您仍然可以使用PIVOT函数来转换数据.如果您知道已知数量的类别，则可以对查询进行硬编码:

You can still use the PIVOT function to transform the data even though the values are strings. If you have a known number of categories, then you can hard-code the query:

select name,
  coalesce(A, '') CategoryA,
  coalesce(B, '') CategoryB,
  coalesce(C, '') CategoryC,
  coalesce(C, '') CategoryD
from
(
  select name, category, 'X' flag
  from yourtable
) d
pivot
(
  max(flag)
  for category in (A, B, C, D)
) piv

请参见带演示的SQL小提琴.

动态枢轴:

如果类别数量未知，则可以使用动态SQL:

If you have an unknown number of categories, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(category)
                    from yourtable
                    group by category
                    order by category
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

select @colsNull = STUFF((SELECT  ', coalesce(' + QUOTENAME(category)+', '''') as '+QUOTENAME('Category'+category)
                    from yourtable
                    group by category
                    order by category
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')


set @query = 'SELECT name, ' + @colsNull + '
              from
             (
                select name, category, ''X'' flag
                from yourtable
            ) x
            pivot
            (
                max(flag)
                for category in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL提琴.

多个联接:

select c1.name,
  case when c1.category is not null then 'X' else '' end as CategoryA,
  case when c2.category is not null then 'X' else '' end as CategoryB,
  case when c3.category is not null then 'X' else '' end as CategoryC,
  case when c4.category is not null then 'X' else '' end as CategoryD
from yourtable c1
left join yourtable c2
  on c1.name = c2.name
  and c2.category = 'B'
left join yourtable c3
  on c1.name = c3.name
  and c3.category = 'C'
left join yourtable c4
  on c1.name = c4.name
  and c4.category = 'D'
where c1.category = 'A'

请参见带有演示的SQL小提琴

所有查询都会给出结果:

All queries will give the result:

| NAME | CATEGORYA | CATEGORYB | CATEGORYC | CATEGORYD |
--------------------------------------------------------
|  Joe |         X |         X |           |         X |
| Mary |         X |           |         X |         X |

这篇关于没有聚合的SQL Server 2012 PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！