没有聚合的 SQL Server 2012 PIVOT

2022-01-03 00:00:00 pivot tsql sql-server sql-server-2012

我有以下示例数据:

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 Fiddle with Demo

静态枢轴:

即使值是字符串，您仍然可以使用 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 Fiddle with Demo.

动态枢轴:

如果您有未知数量的类别，那么您可以使用动态 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 Fiddle with Demo.

多重连接:

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 Fiddle with Demo

所有查询都会给出结果:

All queries will give the result:

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

相关文章