He estado luchando con esto por un tiempo, básicamente tengo una lista de nombres en la base de datos para una identificación determinada, y quiero seleccionar el nombre de una identificación en una columna, y el resto de los nombres de una identificación en una coma separada otra columna. La segunda columna se consulta de la siguiente manera:

SELECT KittyName as KittyName, rowNo = ROW_NUMBER() OVER (Order By (select 1) ASC) FROM 
           (SELECT
                (
                    SELECT KittyName + ','
                    FROM KittyNameTable
                    where KittyNameTable.Id = OtherKittyData.Id
                    FOR XML PATH('')
                ) as CombinedKittyNames
           ) as t2 WHERE rowNum > 1

Esta consulta se ejecuta sin la cláusula rowNum produciendo dos columnas:

KittyName   rowNum
Fluffy       1
Jeff         1
Jeff         2
Marcus       1

Pero cuando lo incluyo dice rowNum no es una columna.

Probé algunas cosas pero no tuve suerte. ¿Algunas ideas?

0
NibblyPig 29 ene. 2015 a las 14:42

2 respuestas

La mejor respuesta

No puede utilizar un alias definido en un select en la cláusula where. Utilice un CTE o una subconsulta en su lugar:

WITH cte as (
      SELECT KittyName as KittyName,
             rowNum = ROW_NUMBER() OVER (Order By (select 1) ASC) 
      FROM (SELECT
                (
                    SELECT KittyName + ','
                    FROM KittyNameTable
                    where KittyNameTable.Id = OtherKittyData.Id
                    FOR XML PATH('')
                ) as CombinedKittyNames
            FROM OtherKittyData
           ) as t2
     )
SELECT *
FROM CTE
WHERE rowNum > 1;

Como nota: probablemente no desee una coma al final de la lista. Además, es más seguro convertir el XML del tipo XML a una cadena de caracteres, por lo que los caracteres como los símbolos y las marcas no se convierten a '&. Esta consulta se vería así:

WITH cte as (
      SELECT ROW_NUMBER() OVER (Order By (select NULL)) as rowNum,
             STUFF((SELECT ',' + KittyName
                    FROM KittyNameTable
                    WHERE KittyNameTable.Id = OtherKittyData.Id
                    FOR XML PATH('concat'), TYPE
                   ).VALUE('/concat[1]', 'varchar(max)'),
                   1, 1, '') as CombinedKittyNames
      FROM OtherKittyData
     )
SELECT *
FROM CTE
WHERE rowNum > 1;

Editar:

Si quieres todos los nombres de gatitos excepto el primero, entonces tienes row_number() en el lugar equivocado. Sugerencia: cuando haga una pregunta, incluya datos de muestra y los resultados deseados.

      SELECT okd.*,
             STUFF((SELECT ',' + KittyName
                    FROM (SELECT knt.*,
                                 ROW_NUMBER() OVER (Order By (select NULL)) as rowNum
                          FROM KittyNameTable knt
                         ) knt
                    WHERE knt.Id = okd.Id AND
                          rowNum > 1
                    FOR XML PATH('concat'), TYPE
                   ).VALUE('/concat[1]', 'varchar(max)'),
                   1, 1, '') as CombinedKittyNames
      FROM OtherKittyData okd;

En otras palabras, debe realizar el filtrado dentro de la subconsulta.

Por cierto, no deberías usar order by con una constante en este caso. No hay una "primera" fila. Debe usar una columna de orden explícita (como una identificación).

0
Gordon Linoff 29 ene. 2015 a las 12:09

Tratar

select KittyName, RowNum
from
    (SELECT KittyName as KittyName, rowNum
           (SELECT
                (
                    SELECT KittyName + ','
                    FROM KittyNameTable
                    where KittyNameTable.Id = OtherKittyData.Id
                    FOR XML PATH('')
                ) as CombinedKittyNames
           ) as t2 ) as x
 Where RowNum = 1
0
Ewan 29 ene. 2015 a las 12:35