SQL Server: Ejemplos de datos de cadenas pivotantes

Tratando de encontrar algunos ejemplos simples de PIVOTE de SQL Server. La mayoría de los ejemplos que he encontrado implican contar o resumir números. Solo quiero pivotar algunos datos de cadena. Por ejemplo, tengo una consulta que devuelve lo siguiente.

Action1 VIEW  
Action1 EDIT  
Action2 VIEW  
Action3 VIEW  
Action3 EDIT

Me gustaría usar PIVOT (si es posible) para hacer los resultados así:

Action1 VIEW EDIT  
Action2 VIEW NULL  
Action3 VIEW EDIT

¿Es esto incluso posible con la funcionalidad de PIVOTE?

Author: Martin Eden, 2008-08-23
Source

6 answers

Recuerde que la función MAX aggregate funcionará tanto en texto como en números. Esta consulta solo requerirá que la tabla se escanee una vez.

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
 164
Author: John Hubert,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-02 19:55:19

Si desea utilizar específicamente la función PIVOT de SQL Server, entonces esto debería funcionar, suponiendo que sus dos columnas originales se llamen act y cmd. (Sin embargo, no es tan bonito para mirar.)

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
 52
Author: Miles D,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-02 20:52:47

Configuración de la tabla: 

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');

Su tabla: SELECT action, view_edit FROM dbo.tbl

Tu mesa

Consulta sin usar PIVOTE: 

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

Consulta mediante PIVOTE: 

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

Resultado de ambas consultas:
introduzca la descripción de la imagen aquí

 52
Author: mxasim,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-08 13:54:37

De 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
 7
Author: saranya,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-03-19 17:56:59

Bueno, para su muestra y cualquiera con un número limitado de columnas únicas, esto debería hacerlo.

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
Author: vzczc,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-23 19:44:24 
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;
 5
Author: mr_eclair,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-12-19 17:57:43