PRACTICA SQL SERVER

Ejemplo

Considere las siguientes tablas (creadas con este SQL):

 SELECT * FROM people
 SELECT * FROM phones

Si escribimos esta consulta, obtenemos 2 resultados de Ned Flanders. Si este fuera un informe, nuestros usuarios pueden quejarse de que Ned Flanders está mostrando dos veces después de añadir el teléfono celular de columna.

 SELECT
      people.id,
      people.first_name,
      people.last_name,
      phones.phone_number as 'cell'
 FROM people
 LEFT OUTER JOIN phones
      ON phones.person_id=people.id
      AND phones.phone_number_type='cell'
 ORDER BY people.id

Identificar el problema 
En nuestro ejemplo, podemos distinguir fácilmente el registro duplicado porque hay tan pocos registros. Pero en un gran conjunto de datos, no seremos capaces de detectar rápidamente los duplicados. La manera más fácil de identificar la presencia de resultados duplicados es comparar un recuento de los id de registros a un recuento distinto de los id de registros:

 SELECT COUNT(id) AS cnt, COUNT(DISTINCT(id)) AS distinct_cnt
 FROM (
      -- previous query here, without ORDER BY clause
      SELECT
           people.id,
           people.first_name,
           people.last_name,
           phones.phone_number as 'cell'
      FROM people
      LEFT OUTER JOIN phones
           ON phones.person_id=people.id
           AND phones.phone_number_type='cell'
 ) results

En este ejemplo, la consulta anterior es poner en una subconsulta. Esto nos ahorra tener que refactorizar la consulta anterior demasiado con el fin de simplemente obtener un recuento de registros. La cláusula ORDER BY tuvo que ser retirado porque SQL Server no permite que sea utilizado en subconsultas. 
El siguiente paso es identificar los ID de registros que están provocando los registros duplicados:

 SELECT *
 FROM (
      SELECT id, COUNT(*) as cnt
      FROM (
           -- previous query here, without ORDER BY clause
           SELECT
                people.id,
                people.first_name,
                people.last_name,
                phones.phone_number as 'cell'
           FROM people
           LEFT OUTER JOIN phones
                ON phones.person_id=people.id
                AND phones.phone_number_type='cell'
      ) results1
      GROUP BY id
 ) results2
 WHERE cnt>1
 ORDER BY cnt DESC

En este ejemplo, hemos tenido que utilizar dos subconsultas. La recóndita subconsulta nos da un recuento del numero de teléfono celular para cada persona id. La siguiente consulta es necesaria para tener una cláusula WHERE para el valor CNT. Sólo estamos interesados en los registros duplicados, por lo que sería cuando el recuento es mayor que 1.

Posible Solución

Primero de todo, tenemos que ignorar uno del teléfono celular registros pertenecientes a Ned Flanders. En una situación real, ojalá hubiéramos preferido del pabellón columna o una columna Fecha de modificación para ayudarnos a elegir el registro que se va a conservar y cuáles ignorar. También querrá asegurarse de que el número de teléfono no es NULL o una cadena vacía. En este ejemplo, supongamos que el mayor valor de id es la más reciente y, por lo tanto, la mayoría de registros precisos.

Utilizar un SELECT dentro de la instrucción SELECT.

Una forma de solucionar este problema es utilizar una instrucción SELECT dentro de la instrucción SELECT. De esta manera, se consigue sólo un teléfono celular para cada persona id.

 SELECT
     people.first_name,
     people.last_name,
     (
      SELECT TOP 1 phone_number
      FROM phones
      WHERE phones.person_id=people.id
           AND phones.phone_number_type='cell'
           AND phone_number IS NOT NULL
           AND RTRIM(LTRIM(phone_number))!=''
      ORDER BY phones.id DESC
     ) AS 'cell_phone'
 FROM people
 ORDER BY people.id

En este ejemplo, estamos seleccionando sólo un teléfono celular registro por persona. ¿Qué determina la celda seleccionada teléfono registro es la cláusula ORDER BY; puedes poner tu lógica de negocio aquí para reducirlo a un resultado por persona. Es importante tener chequeos adicionales en la cláusula WHERE, como estamos omitiendo los registros en un intento de seleccionar el mejor registro. No querríamos para seleccionar un valor nulo, sólo porque era la más reciente.

vamos a empezar con el query:

 SELECT 
      id,
      person_id,
      phone_number,
      ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row
 FROM phones
 WHERE phone_number_type='cell'
      AND phone_number IS NOT NULL
      AND LTRIM(RTRIM(phone_number)) != ''

Él parte especial en esta consulta es utilizar el número de la ROW NUMBER() OVER (...) la sintaxis para obtener un número de fila para cada persona. Es algo raro sintaxis; ayuda a pensar que  PARTITION BY como una cláusula GROUP BY. La idea es conseguir una nueva columna denominada fila con un orden con prioridad de 1...n para cada persona id. A continuación, todo lo que tenemos que hacer es tener una cláusula WHERE para especificar que la fila=1. 
De nuevo, verá el mismo restringir la cláusula WHERE y la misma cláusula ORDER BY como antes.

query final:

 SELECT id, person_id, phone_number
 FROM (
      SELECT
           id,
           person_id,
           phone_number,
           ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row
      FROM phones
      WHERE phone_number_type='cell'
           AND phone_number IS NOT NULL
           AND LTRIM(RTRIM(phone_number)) != ''
 ) results
 WHERE row=1

La consolidación de varios resultados

Digamos que queríamos mostrar cada uno de los números de teléfono en uno de los campos, separados por comas. ¿Cómo lo hacemos?

 SELECT people.*, SUBSTRING(phone_list,1,LEN(phone_list)-1) AS cell_phones
 FROM people
 CROSS APPLY (
      SELECT phone_number+', '
      FROM phones
      WHERE person_id=people.id
           AND phone_number_type = 'cell'
           AND phone_number IS NOT NULL
           AND LTRIM(RTRIM(phone_number)) != ''
      ORDER BY phones.id DESC
      FOR XML PATH('')
 ) results (phone_list)

Esto es bastante extraño consulta. Como puede ver, los resultados son lo que esperábamos, pero ¿cómo hemos llegado aquí? Si nos fijamos en la línea 1, estamos seleccionando todo lo de la tabla personas, junto con una subcadena de la lista de teléfonos. La lista de teléfonos de la columna es una concatenación de un número de teléfono, una coma y un espacio. Si no tomamos una subcadena de la lista de teléfonos de columna, que realmente terminan con una coma y un espacio, lo cual no es deseable. 
La Cruz aplicar parte de la consulta es lo que devuelve la coma se unieron a los números de teléfono. Funciona de forma similar a una combinación, pero le permite invocar una función con valores de tabla para cada fila. La sintaxis para el cierre final del paréntesis en la línea 12 es lo que nos permite trabajar con la columna con el nombre de lista de teléfonos en línea 1. 
El FOR XML PATH('') es parte de la consulta en línea 11 concatena nuestras filas juntos como una cadena. Esto es realmente un hack de SQL Server es la capacidad de salida XML. 
El FOR XML PATH() espera un nombre de etiqueta para que pueda hacer un nodo XML con una etiqueta de inicio y otra de fin; también se une a varias filas juntos como una cadena de texto. Estamos usando una cadena en blanco y por lo tanto no cree una etiqueta, pero no se unen varias filas como una cadena de texto. SQL Server también desea agregar una etiqueta de inicio y otra de fin con el nombre de columna por defecto, pero hemos anexado una coma y un espacio para el número de teléfono seleccione, creando un anónimo selección y por lo tanto evitarse otra etiqueta XML que SQL Server normalmente insertar.