none
Como pasar Filas a Columnas? RRS feed

  • Pregunta

  • Hola

    Tengo una duda respecto a una consulta en SQL que estoy haciendo.

    Tengo una tabla en la cual se alamcena la informacion de la siguiente manera

    ID        |  NAME     | IN_OUT                           |

    0001  JULIETA   2018-09-07 16:37:59.080
    0001  JULIETA   2018-09-07 09:36:14.867
    0001  JULIETA   2018-09-08 02:34:07.823
    0001  JULIETA 2018-09-6008 09:08:28.1


    Estoy haciendo un reporte en la cual tengo que mostrar la informacion de la siguiente manera

    ID        |  NAME       | IN                           |     OUT    

    0001  JULIETA   2018-09-07 09:36:14.867   2018-09-07 16:37:59.080          
    0001  JULIETA  2018-09-6008 09:08:28.1    2018-09-08 02:34:07.823

    Existe alguna funcion que me ayude a convertir las filas a columnas de esta manera?

    Gracias!


    • Editado YST2 lunes, 10 de septiembre de 2018 22:39
    lunes, 10 de septiembre de 2018 22:38

Respuestas

  • Hola YST2:

    Para ese ejemplo la solución que te doy, en lo único que cambia, es que no te hace falta evaluar el case en la entrada.

    ;WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id) AS fila,
                a.ID,
                a.name,
                a.in_out,
                LEAD(a.in_out) OVER(PARTITION BY a.id ORDER BY a.in_out) AS siguiente
    			
         FROM in_out AS a
    	 
         SELECT c.id,
                C.NAME,
                c.in_out
                 AS [IN], 
    			 case when c.siguiente IS null then '19000101'
    			 else
                 c.siguiente end
                AS [OUT]
         FROM cte AS c
         WHERE 
    		c.fila % 2 <> 0;
    Un saludo

    • Marcado como respuesta YST2 miércoles, 12 de septiembre de 2018 12:59
    martes, 11 de septiembre de 2018 13:46
  • Con una versión anterior a 2012 la solución es tal que:

    ;WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id, a.in_out) AS fila,
                a.ID,
                a.name,
                a.in_out
    			
    			
         FROM in_out AS a)
    	 
         SELECT c.id,
                C.NAME,
                c.in_out
                 AS [IN], 
    			 case when d.in_out IS null then '19000101'
    			 else
                 d.in_out end
                AS [OUT]
         FROM cte AS c left join cte as d on c.id = d.id and c.fila = d.fila -1
         WHERE 
    		c.fila % 2 <> 0;

    Si tu versión es de 2012 o superior, la base de datos tiene un nivel de compatibilidad, que estará establecido a algo anterior a 110

    o mediante transact sql (Nivel de compatibilidad)

    SELECT compatibility_level  
    FROM sys.databases WHERE name = 'nombreBBDD';  
    GO  
    
    Un saludo

    • Marcado como respuesta YST2 miércoles, 12 de septiembre de 2018 21:42
    miércoles, 12 de septiembre de 2018 13:25

Todas las respuestas

  • Hola YST2

    Eso no parece PIVOT de filas a columnas.

    Yo veo un siguiente registro. (LEAD), desde SQL 2012. Te lo explico directamente con código.

    /*CREACION DEL ESCENARIO*/
    create table in_out (id varchar(4), name varchar(50), in_out datetime)
    go
    insert into in_out (id, name, in_out)
    values
     ('0001','JULIETA','20180907 16:37:59.080'),
     ('0001','JULIETA','20180907 09:36:14.867'),
     ('0001','JULIETA','20180908 02:34:07.823'),
     ('0001','JULIETA','20180908 09:08:28.1')
     go
    
     /*EN UNA TABLA DE EXPRESION COMÚN*/
    ;WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id) AS fila,/*NUMERO LOS REGISTROS*/
                a.ID,
                a.name,
                a.in_out,
                LEAD(a.in_out) OVER(PARTITION BY a.id ORDER BY a.in_out) AS siguiente
    			/*UTILIZO LA FUNCION LEAD PARA OBTENER EL SIGUIENTE POR SU FECHA*/
         FROM in_out AS a
    	 )/*FIN DE LA DECLARACIÓN DEL CTE*/
         SELECT c.id,
                C.NAME,
                CASE/*UTILIZO UN CASE PARA OBTENER UN VALOR U OTRO DEPENDENDIENDO DE LA CONDICIÓN*/
                    WHEN c.in_out > c.siguiente 
                    THEN c.in_out /*EN EL EJEMPLO NO ESTA NADA CLARO ESTA ACCION */
                    ELSE c.siguiente /*PERO TE PONGO LA POSIBILIDAD*/
                END AS [IN], /*LA SALIDA DEL CASE LA LLAMO COLUMNA IN*/
                CASE
    				WHEN c.siguiente IS NULL 
    					THEN '19000101' /*SI NO HAY SIGUIENTE PORQUE SOLO HAY ENTRADA ????*/
                    WHEN c.in_out < c.siguiente
                    THEN c.in_out
                    ELSE c.siguiente
                END AS [OUT]
         FROM cte AS c
         WHERE 
    		c.fila % 2 <> 0;/*SOLO OBTENGO FILAS IMPARES.*/
      

    Como en tu ejemplo no esta nada claro si la 4 entrada es de una fecha posterior o anterior a la tercera, se evalúan en las filas del cte, las posibilidades para que puedas invertir uno u otro resultado. Pero si no es así, entonces, no tienes porque utilizar los case.

    Un saludo

    martes, 11 de septiembre de 2018 5:00
  • Hola

    Si, explico un poco mas detallado el ejemplo

    La tabla consiste en registrar las entradas y salidas de empleados de una tienda y el reporte consiste en mostrar la informacion mas detallada, es decir que muestre en un intervalo de una semana la fecha y hora que salio y entro durante ese dia. 

    Ejemplo

    Si Julienta entro el 2018-09-07 a las 9 y salio 2018-09-07  a las 2 en la tabla se guerda la fecha en una misma columna y en el reporte esta se tiene que mostrar en dos columnas, una que corresponde a la entrada y otra a la salida. 

    En caso de que Julieta entrara el 2018-09-07 a las 9 y saliera el  2018-09-07  as las 2 pero vuelve a entrar a las 4 y salir a las 8 de ese mismo dia, entonces corresponde a otra fila.

    Fila | ID |Nombre | Entrada                   |  Salida

    1   0001   Julieta 2018-09-07 09:00:00  2018-09-07 02:00:00

    2   0001   Julieta 2018-09-07 04:00:00  2018-09-07 08:00:00


    Muchas gracias por tu respuesta

    martes, 11 de septiembre de 2018 13:35
  • Hola YST2:

    Para ese ejemplo la solución que te doy, en lo único que cambia, es que no te hace falta evaluar el case en la entrada.

    ;WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id) AS fila,
                a.ID,
                a.name,
                a.in_out,
                LEAD(a.in_out) OVER(PARTITION BY a.id ORDER BY a.in_out) AS siguiente
    			
         FROM in_out AS a
    	 
         SELECT c.id,
                C.NAME,
                c.in_out
                 AS [IN], 
    			 case when c.siguiente IS null then '19000101'
    			 else
                 c.siguiente end
                AS [OUT]
         FROM cte AS c
         WHERE 
    		c.fila % 2 <> 0;
    Un saludo

    • Marcado como respuesta YST2 miércoles, 12 de septiembre de 2018 12:59
    martes, 11 de septiembre de 2018 13:46
  • Hola

    Una duda, cuando ejecuto el query me manda este mensaje de error

    'LEAD' is not a recognized built-in function name.

    Existe alguna alternativa para la funcion LEAD?

    Gracias

    miércoles, 12 de septiembre de 2018 13:04
  • Si existen alternativas, pero la primera pregunta, es ¿Qué versión de sql server tienes?

    select @@versión

    miércoles, 12 de septiembre de 2018 13:14
  • me aparece este

    Microsoft SQL Server 2005 - 9.00.4060.00 (Intel X86)   Mar 17 2011 13:20:38   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 6.0 (Build 6002: Service Pack 2) 

    miércoles, 12 de septiembre de 2018 13:16
  • Con una versión anterior a 2012 la solución es tal que:

    ;WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id, a.in_out) AS fila,
                a.ID,
                a.name,
                a.in_out
    			
    			
         FROM in_out AS a)
    	 
         SELECT c.id,
                C.NAME,
                c.in_out
                 AS [IN], 
    			 case when d.in_out IS null then '19000101'
    			 else
                 d.in_out end
                AS [OUT]
         FROM cte AS c left join cte as d on c.id = d.id and c.fila = d.fila -1
         WHERE 
    		c.fila % 2 <> 0;

    Si tu versión es de 2012 o superior, la base de datos tiene un nivel de compatibilidad, que estará establecido a algo anterior a 110

    o mediante transact sql (Nivel de compatibilidad)

    SELECT compatibility_level  
    FROM sys.databases WHERE name = 'nombreBBDD';  
    GO  
    
    Un saludo

    • Marcado como respuesta YST2 miércoles, 12 de septiembre de 2018 21:42
    miércoles, 12 de septiembre de 2018 13:25
  • Hola

    me ha funcionado muy bien el query, gracias

    Realice una modificacion para poder ordenar las filas donde la entrada y salida correspondan al mismo dia en caso contrario poner null en salida. Pero se pierden algunos datos en el proceso, al parecer null remplaza al valor que se supone que debe ir en la columna OUT y ese no se pone coloca en la siguiente fila

    Este es mi query

    WITH cte
         AS (
         SELECT ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.id, a.in_out) AS fila,
                a.ID,
                a.name,
                a.in_out
         FROM in_out AS a)
    	 
         SELECT c.id,
                C.NAME,
                c.in_out
                 AS [IN], 
    			 case when day(C.in_out) <> day(d.in_out) then null
    			 else
                 d.in_out end
                AS [OUT]
         FROM cte AS c left join cte as d on c.id = d.id and c.fila = d.fila -1
         WHERE 
    		c.fila % 2 <> 0;

    Como podria colocar el valor que null sustituyo en la columna out, poner dicho valor en la columan IN pero en la siguiente fila?

    Gracias por la ayuda!


    • Editado YST2 miércoles, 12 de septiembre de 2018 21:47
    miércoles, 12 de septiembre de 2018 21:42