SQL Server:旋转字符串数据的示例

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following.

尝试找到一些简单的SQL Server PIVOT示例。我发现的大多数例子都涉及数或加和数字。我只是想对一些字符串数据进行透视。例如，我有一个查询返回以下内容。

Action1 VIEW  
Action1 EDIT  
Action2 VIEW  
Action3 VIEW  
Action3 EDIT  

I would like to use PIVOT (if even possible) to make the results like so:

我想使用PIVOT(如果可能的话)使结果是这样的:

Action1 VIEW EDIT  
Action2 VIEW NULL  
Action3 VIEW EDIT  

Is this even possible with the PIVOT functionality?

这对PIVOT功能是否可行?

6 个解决方案

#1

Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.

记住，最大聚合函数可以处理文本和数字。此查询只需要扫描表一次。

SELECT Action,
       MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol, 
       MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
 FROM t
 GROUP BY Action

#2

If you specifically want to use the SQL Server PIVOT function, then this should work, assuming your two original columns are called act and cmd. (Not that pretty to look at though.)

如果您特别想使用SQL Server PIVOT函数，那么这应该起作用，假设您的两个原始列被称为act和cmd。(不过看起来不怎么好看。)

SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'
FROM (
    SELECT act, cmd FROM data
) AS src
PIVOT (
    MAX(cmd) FOR cmd IN ([View], [Edit])
) AS pvt

#3

Table setup:

表设置:

CREATE TABLE dbo.tbl (
    action VARCHAR(20) NOT NULL,
    view_edit VARCHAR(20) NOT NULL
);

INSERT INTO dbo.tbl (action, view_edit)
VALUES ('Action1', 'VIEW'),
       ('Action1', 'EDIT'),
       ('Action2', 'VIEW'),
       ('Action3', 'VIEW'),
       ('Action3', 'EDIT');

Your table: SELECT action, view_edit FROM dbo.tbl

表:从dbo.tbl中选择action, view_edit

Query without using PIVOT:

查询不使用主:

SELECT Action, 
[View] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'VIEW'),
[Edit] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'EDIT')
FROM tbl t
GROUP BY Action

Query using PIVOT:

使用主查询:

SELECT [Action], [View], [Edit] FROM
(SELECT [Action], view_edit FROM tbl) AS t1 
PIVOT (MAX(view_edit) FOR view_edit IN ([View], [Edit]) ) AS t2

Both queries result:

这两个查询结果:

#4

From http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/:

从http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/:

SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO

#5

Well, for your sample and any with a limited number of unique columns, this should do it.

对于你的样本和任何有有限的唯一列的样本，应该这样做。

select 
    distinct a,
    (select distinct t2.b  from t t2  where t1.a=t2.a and t2.b='VIEW'),
    (select distinct t2.b from t t2  where t1.a=t2.a and t2.b='EDIT')
from t t1

#6

With pivot_data as
(
select 
action, -- grouping column
view_edit -- spreading column
from tbl
)
select action, [view], [edit]
from   pivot_data
pivot  ( max(view_edit) for view_edit in ([view], [edit]) ) as p;