TSQL 表转换字段 =>专栏

Question

我有以下表格布局。每行值始终是唯一的。相同 Id、Name 和 Line 的实例永远不会超过一个。

Id Name Line
1  A    Z
2  B    Y
3  C    X
3  C    W
4  D    W

我想查询数据，使 Line 字段成为一列。如果值存在，则在字段数据中应用 1，否则应用 0。例如，

Id Name Z Y X W
1  A    1 0 0 0
2  B    0 1 0 0
3  C    0 0 1 1
4  D    0 0 0 1

字段名称 W、X、Y、Z 只是字段值的示例，因此我无法应用运算符来显式检查，例如、“X”、“Y”或“Z”。这些可能随时改变，并且不限于一组有限的值。结果集中的列名称应将唯一字段值反映为列。

知道我怎样才能做到这一点吗？

Answer 1

这是一个标准的枢轴查询。

如果 1 表示布尔指示符 - 使用：

  SELECT t.id,
         t.name,
         MAX(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         MAX(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         MAX(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         MAX(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

如果 1 表示该组具有该值的记录数，则使用：

  SELECT t.id,
         t.name,
         SUM(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         SUM(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         SUM(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         SUM(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

Answer 2

已编辑以下相关更新

SQL Server 不支持动态旋转。

为此，您可以使用动态 SQL 来生成如下查询。

SELECT 
       Id ,Name, 
       ISNULL(MAX(CASE WHEN Line='Z' THEN 1 END),0) AS Z,
       ISNULL(MAX(CASE WHEN Line='Y' THEN 1 END),0) AS Y,
       ISNULL(MAX(CASE WHEN Line='X' THEN 1 END),0) AS X,
       ISNULL(MAX(CASE WHEN Line='W' THEN 1 END),0) AS W
FROM T
 GROUP BY Id ,Name

或者我已经读过但没有实际尝试过的另一种方法是利用 Access Transform 功能，通过设置一个 Access 数据库，并使用指向 SQL Server 表的链接表，然后从 SQL Server 查询 Access 数据库！

Answer 3

这是动态版本

测试表

create table #test(id int,name char(1),line char(1))

insert #test values(1 , 'A','Z')
insert #test values(2 , 'B','Y')
insert #test values(3 , 'C','X')
insert #test values(4 , 'C','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','P')

现在运行这个

declare @names nvarchar(4000)

SELECT @names =''
  SELECT  @names    = @names +   line +', '  
    FROM (SELECT distinct  line from #test) x

SELECT @names = LEFT(@names,(LEN(@names) -1))

exec('
SELECT *
 FROM(
SELECT DISTINCT Id, Name,Line
FROM #test
    ) AS pivTemp
PIVOT
(   COUNT(Line)
    FOR Line IN (' + @names +' )
) AS pivTable ')

现在向表中添加一行并再次运行上面的查询，您将看到 B

insert #test values(5 , 'D','B')

警告：当然，动态 SQL 的所有问题都适用，如果您可以使用 sp_executeSQL 但由于参数不像查询中那样使用，所以确实没有意义

Answer 4

假设您可以枚举有限数量的 Line 值：

declare @MyTable table (
    Id int,
    Name char(1),
    Line char(1)
)

insert into @MyTable
    (Id, Name, Line)
    select 1,'A','Z'
    union all
    select 2,'B','Y'
    union all
    select 3,'C','X'
    union all
    select 3,'C','W'
    union all
    select 4,'D','W'

SELECT Id, Name, Z, Y, X, W
    FROM (SELECT Id, Name, Line
            FROM @MyTable) up
    PIVOT (count(Line) FOR Line IN (Z, Y, X, W)) AS pvt
    ORDER BY Id

Answer 5

当您使用 SQL Server 时，您可以使用 PIVOT 运算符旨在用于此目的。

Answer 6

如果您正在为 SQL Server Reporting Services (SSRS) 报表执行此操作，或者可能转而使用该报表，那么现在就停止并在报表中添加一个 Matrix 控件。噗！你完成了！对数据的转变感到高兴。

Answer 7

这是一种相当奇特的方法（使用旧 Northwind 数据库中的示例数据）。它改编自版本 此处，由于 DBCC RENAMECOLUMN 的弃用以及 PIVOT 作为关键字的添加而不再有效。

set nocount on 
create table Sales ( 
  AccountCode char(5), 
  Category varchar(10), 
  Amount decimal(8,2) 
) 
--Populate table with sample data 
insert into Sales 
select customerID, 'Emp'+CAST(EmployeeID as char), sum(Freight) 
from Northwind.dbo.orders 
group by customerID, EmployeeID 
create unique clustered index Sales_AC_C 
on Sales(AccountCode,Category) 
--Create table to hold data column names and positions 
select A.Category, 
       count(distinct B.Category) AS Position 
into #columns 
from Sales A join Sales B 
on A.Category >= B.Category 
group by A.Category 
create unique clustered index #columns_P on #columns(Position) 
create unique index #columns_C on #columns(Category) 
--Generate first column of Pivot table 
select distinct AccountCode into Pivoted from Sales 
--Find number of data columns to be added to Pivoted table 
declare @datacols int 
select @datacols = max(Position) from #columns 
--Add data columns one by one in the correct order 
declare @i int 
set @i = 0 
while @i < @datacols begin 
  set @i = @i + 1 
--Add next data column to Pivoted table 
  select P.*, isnull(( 
    select Amount 
    from Sales S join #columns C 
    on C.Position = @i 
    and C.Category = S.Category 
    where P.AccountCode = S.AccountCode),0) AS X 
  into PivotedAugmented 
  from Pivoted P 
--Name new data column correctly 
  declare @c sysname 
  select @c = Category 
  from #columns 
  where Position = @i 
  exec sp_rename '[dbo].[PivotedAugmented].[X]', @c, 'COLUMN'
--Replace Pivoted table with new table 
  drop table Pivoted 
  select * into Pivoted from PivotedAugmented 
  drop table PivotedAugmented 
end 
select * from Pivoted 
go 
drop table Pivoted 
drop table #columns 
drop table Sales