Ajuda em SELECT

Question

Boas..

Tenho uma tabela onde registo os serviços prestados a pessoas, identificado pelo campo NProcesso e outro campo em que defino se alguem da sua famila também foi atendido.

Nprocesso	ElementoFamilia	Servico
1	1,2	asdasd
1	1	sada
2	2,4	asdasd
3	1,3	asdasd
3	1,2	d
3	1	d

Preciso de mostrar o número de pessoas atendidas..

Referente ao NProcesso 1, ele teria que contar 2 pessoas, o elementofamilia 1 e o 2

Referente ao NProcesso 2, ele teria que contar 2 pessoas, o elementofamilia 2 e o 4

Referente ao NProcesso 3, ele teria que contar 3 pessoas, o elementofamilia 1, o 2 e o 3

Como posso fazer isto?

Answer 1

Bom Dia,

 

Se os dados são gravados delimitados por "," isso é um mal sinal, pois, denota um erro de modelagem e tipicamente torna as consultas mais complicadas (a exemplo dessa). O correto seria não ter esses dados multivalorados em um único campo.

 

Há como fazer e pode ser mais fácil ou mais difícil. Qual a versão do seu SQL Server ?

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 2

SQL EXPRESS 2005

O mais correcto seria ter um registo para cada elemento da familia?

Answer 3

Olá Vitor Martins,

 

Seguramente o mais correto é fazer a separação. Os princípios de normalização não aceitam valores multivalorados em uma única coluna de um único registro. Veja como a separação torna as coisas bem mais simples

 

Code Snippet

declare @ElementoFamilia table (

Nprocesso int, ElementoFamilia int, Servico varchar(20))

 

insert into @ElementoFamilia values (1,1,'asdasd')

insert into @ElementoFamilia values (1,2,'asdasd')

insert into @ElementoFamilia values (1,1,'sada')

insert into @ElementoFamilia values (2,2,'asdasd')

insert into @ElementoFamilia values (2,4,'asdasd')

insert into @ElementoFamilia values (3,1,'asdasd')

insert into @ElementoFamilia values (3,3,'asdasd')

insert into @ElementoFamilia values (3,1,'d')

insert into @ElementoFamilia values (3,2,'d')

insert into @ElementoFamilia values (3,1,'d')

 

select NProcesso, COUNT(DISTINCT ElementoFamilia) As QTD

FROM @ElementoFamilia GROUP BY NProcesso

 

 

E veja como ter tudo junto torna as coisas bem mais difíceis e menos performáticas

 

Code Snippet

declare @ElementoFamilia table (

Nprocesso int, ElementoFamilia varchar(10), Servico varchar(20))

 

insert into @ElementoFamilia values (1,'1,2','asdasd')

insert into @ElementoFamilia values (1,'1','sada')

insert into @ElementoFamilia values (2,'2,4','asdasd')

insert into @ElementoFamilia values (3,'1,3','asdasd')

insert into @ElementoFamilia values (3,'1,2','d')

insert into @ElementoFamilia values (3,'1','d')

 

;With ElementoFamiliaXML (EFXML) AS (

select CAST(

'<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

REPLACE(ElementoFamilia,',','</e><e>') + '</e></E>' AS XML)

from @ElementoFamilia),

 

Res (NRProcesso, ElementoFamilia) AS (

select

X.value('../@NProcesso','int') As NRProcesso,

X.value('.','int') As ElementoFamilia

from ElementoFamiliaXML As EFXML1

CROSS APPLY

EFXML.nodes('/E/e') As EFXML2 (X))

 

select nrprocesso, count(distinct ElementoFamilia) as qtd

from Res group by nrprocesso

 

Isso sem contar os problemas de atualização e exclusão. Fica complicado atualizar somente um elemento em uma cadeia de elementos separada por ;

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

Answer 4

Pois..É muito mais fácil..quando construia tabela não me lembrei disso..O objectivo era ter um campo descritivo que permitisse fazer consultas, mas não me lembrei como as iria fazer..

Não percebi bem que parte do código tenho de introduzir..

;With ElementoFamiliaXML (EFXML) AS (

select CAST(

'<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

REPLACE(ElementoFamilia,',','</e><e>') + '</e></E>' AS XML)

from @ElementoFamilia),

 

Res (NRProcesso, ElementoFamilia) AS (

select

X.value('../@NProcesso','int') As NRProcesso,

X.value('.','int') As ElementoFamilia

from ElementoFamiliaXML As EFXML1

CROSS APPLY

EFXML.nodes('/E/e') As EFXML2 (X))

 

select nrprocesso, count(distinct ElementoFamilia) as qtd

from Res group by nrprocesso

Answer 5

Olá Vitor,

 

Esse código é para o meu exemplo. Você terá que adaptar ao seu exemplo.

Esse código converter os elementosfamilia para xml e faz a delimitação e para posteriormente aplicar o COUNT.

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 6

Adaptei e coloquei num SP

Dá os seguinte erro

Msg 207, Level 16, State 1, Procedure experiencia, Line 21

Invalid column name 'EFXML'.

Msg 9506, Level 16, State 1, Procedure experiencia, Line 21

The XMLDT method 'nodes' can only be invoked on columns of type xml.

Answer 7

Tenho assim

CREATE PROCEDURE dbo.experiencia 

AS

BEGIN

SET NOCOUNT ON;

;With ElementoFamiliaXML (EFXML) AS 

(select CAST('<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

REPLACE(elementosaf,',','</e><e>') + '</e></E>' AS XML)

from servicos_prestados), Res (NProcesso, elementosaf) AS 

(select X.value('../@NProcesso','int') As NProcesso,

X.value('.','int') As elementosaf

from servicos_prestados As EFXML1

CROSS APPLY EFXML.nodes('/E/e') As EFXML2 (X))

 

select NProcesso, count(distinct elementosaf) as qtd

from Res 

group by NProcesso

END

Answer 8

Olá Vitor Martins,

 

Você declarou a CTE ElementoFamiliaXML, mas usou a tabela servicos_prestados para executar o método Nodes. Tente da seguinte forma:

 

Code Snippet

CREATE PROCEDURE dbo.experiencia

 

AS

BEGIN

 

SET NOCOUNT ON;

 

;With ElementoFamiliaXML (EFXML) AS

(select CAST('<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

REPLACE(elementosaf,',','</e><e>') + '</e></E>' AS XML)

from servicos_prestados),

 

Res (NProcesso, elementosaf) AS

(select X.value('../@NProcesso','int') As NProcesso,

X.value('.','int') As elementosaf

from ElementoFamiliaXML As EFXML1

CROSS APPLY EFXML.nodes('/E/e') As EFXML2 (X))

 

 

select NProcesso, count(distinct elementosaf) as qtd

from Res

group by NProcesso

 

END

 

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 9

Obrigado..

Era isso mesmo..

Obrigado Mestre..

Só uma ultima pergunta qual seria a melhor estrutura para a tabela?

Answer 10

Olá Vitor,

 

Que bom que lhe atendeu. Por favor classifique a resposta.

Uma estrutura mais adequada vai depender das regras (não posso arbitrar sem conhecer suas regras de negócio). Se esse for o caso, especifique-as e abra uma nova thread (são dúvidas separadas).

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com