Passar dados de linha para coluna

Question

Boa Tarde!!!

 

Gostaria de saber como faço para passar linha para coluna no seguinte caso:

Possuo uma tabela(tabela 1) aonde é permitido inserir registro aonde cada registro seria o

nome de um campo para um programa e em outra tabela (tabela 2)fica o valor para cada campo.

 

Estrutra da Tabela 1

IdTabela1 int

NomeCampo varchar(100)

 

Estrutura da Tabela 2

IdTabela1 int

IdTabela2 int

valordocampo varchar(100)

IdPessoa int

 

Exemplo de valores:

Tabela1

IdTabela1 NomeCampo

1 Telefone

2 Endereco

3 Cidade

 

Tabela 2

IdTabela1 IdTabela2 ValorCampo idPessoa

1 1 2020-2020 1

1 2 1515-1515 2

2 3 Rua B 1

3 4 Rio de Janeiro 1

3 5 Sao Paulo 2

 

- Gostaria de ter como resultado

como colunas:           IdPessoa       Telefone         Endereco        Cidade

como resultados:       1                    2020-2020        Rua B              Rio de Janeiro

                                2                    1515-1515                               Sao Paulo

 

Answer 1

Olá Jefferson,

 

Me parece que você está utilizando uma técnica de modelagem Entidade Atributo Valor. Qual seria a versão do seu SQL Server ? Dependendo da resposta, pode haver uma solução específica para o seu caso.

 

[ ]s,

 

Gustavo

Answer 2

 

A versão do SQL Server é 2000, mas tb tem que funcionar para 2005.

Answer 3

Olá Jeferson,

 

O SQL Server 2005 está equiparado com o operador PIVOT que entre outras coisas, é ótimo para transformar estruturas EAV (como o seu modelo) em uma estrutura relacional simples. Eles também podem ser emulados no SQL Server 2000 através de subqueries.

 

O problema é que no seu caso (SQL Server 2000), o número de colunas não é fixo e isso poderá dificultar as coisas. Receio que o esforço em TSQL envolvido seja muito grande e talvez a solução não seja satisfatória. O 2005 experimente algumas dessas dificuldades, mas com o PIVOT as coisas ficam um pouco mais fáceis.

 

[ ]s,

 

Gustavo

 

 

Answer 4

nao sei se ajuda mais seque um exemplo ( ja postado aqui no forum ).

 

Cara o que vc. procura e fazer deve ser feito usando uma ferramenta de BI ( que e o mais adequado ), assim vc. transforma as linhas em colunas, sem problemas por exemplo o analisys services e a ferramenta do sql server para isso simples de usar e criada para trabalhar com dados da maneira que vc. precisa, uma outra ideia e no sql 2005 usar a funcao pivot mais vc. precisa saber quais os anos vc. quer "pivotear", entao fiz esta rotina que transforma as linhas em colunas de maneira dinamica, na pratica a performance nao e boa mais ajuda quando precisamos trazer nest tipo de formato segue:

 

-- Esta funcao e para retornar somente o dia 01 de todos os meses
CREATE FUNCTION fn_RBMS_ReturnMonth (@Vdata as Datetime)
RETURNS DateTime
AS
BEGIN
return cast(Str(month(@Vdata)) + '/01/'+ ltrim(Str(year(@Vdata))) as SMALLDateTime)
END
-- -- Cria uma Tabela de Exemplo
Create Table #Dados (Idx int identity(1,1), Valor Numeric (19,2), Data DateTime)
Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-01')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-02-01')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-04-01')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-01')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-02')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-07')
Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-08')

Create Table #PivotTabela (CampoChave Int)
Insert into #PivotTabela (CampoChave) Values (1) -- So para criar uma linha em branco
Declare @DataI Datetime -- Sao Variaveis para fazer o loop
Declare @DataF Datetime --
Select @DataI = Dbo.fn_RBMS_ReturnMonth(Min(Data)), @DataF = Dbo.fn_RBMS_ReturnMonth(Max(Data))
From #Dados

-- Alimenta com a menor e a maior data para definir o range
Declare @TotalMes Numeric(19,2)
Declare @StringDDL Varchar(1000)
While @DataI <= @DataF
Begin
Select @TotalMes = Sum(Valor) From #Dados
Where Dbo.fn_RBMS_ReturnMonth(Data) = @DataI
If @TotalMes > 0
Begin
Set @StringDDL = 'Alter Table #PivotTabela Add [' + Right(Convert(Char(10),@DataI,105),7) + '] Numeric(19,2) '
Exec(@StringDDL)
Set @StringDDL = 'Update #PivotTabela Set [' + Right(Convert(Char(10),@DataI,105),7) + '] = ' + convert(varchar(30),@Totalmes)
Exec(@StringDDL)
Set @TotalMes = 0
End
Set @DataI = DateAdd(m,1, @DataI)
End

Select * From #PivotTabela

 

qq retorne.

 

Answer 5

Entendi,

Mas tem como vc me mandar um modelo de como seria a query em sql server 2000 com subquerys?

 

 

Abs.

 

 

Answer 6

Jeferson,

 

Veja este exemplo para utilizar Pivot no SQL Server 2005.

 

Code Snippet

Create table #Exemplo (codigo int, nome varchar(10))

 

insert into #Exemplo (codigo, nome) Values (1,'jose')

insert into #Exemplo (codigo, nome) Values (2,'mario')

insert into #Exemplo (codigo, nome) Values (1,'jose')

insert into #Exemplo (codigo, nome) Values (2,'mario')

insert into #Exemplo (codigo, nome) Values (3,'celso')

insert into #Exemplo (codigo, nome) Values (4,'andre')

 

Select [jose],[mario],[celso],[andre] from #exemplo

Pivot (count(codigo) for nome in ([jose],[mario],[celso],[andre])) p

 

 

Answer 7

Olá Jefferson,

 

Esse tipo de técnica é limitado e cheio de problemas (indexação por exemplo). No livro "Inside SQL Server 2005: Query Tuning" é demonstrado como resolver esse problema com o uso do Pivot no SQL Server 2005. O autor também mostra como fazer no SQL Server 2000. O máximo que podemos chegar é em uma solução parecida com a citada no livro:

 

Code Snippet

CREATE TABLE dbo.OpenSchema

(

objectid INT NOT NULL,

attribute NVARCHAR(30) NOT NULL,

value SQL_VARIANT NOT NULL,

PRIMARY KEY (objectid, attribute)

);

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(1, N'attr1', CAST('ABC' AS VARCHAR(10)) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(1, N'attr2', CAST(10 AS INT) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(1, N'attr3', CAST('20040101' AS SMALLDATETIME));

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(2, N'attr2', CAST(12 AS INT) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(2, N'attr3', CAST('20060101' AS SMALLDATETIME));

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(2, N'attr4', CAST('Y' AS CHAR(1)) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(2, N'attr5', CAST(13.7 AS DECIMAL(9,3)) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(3, N'attr1', CAST('XYZ' AS VARCHAR(10)) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(3, N'attr2', CAST(20 AS INT) );

 

INSERT INTO dbo.OpenSchema(objectid, attribute, value)

VALUES(3, N'attr3', CAST('20050101' AS SMALLDATETIME));

 

SELECT objectid,

MAX(CASE WHEN attribute = 'attr1' THEN value END) AS attr1,

MAX(CASE WHEN attribute = 'attr2' THEN value END) AS attr2,

MAX(CASE WHEN attribute = 'attr3' THEN value END) AS attr3,

MAX(CASE WHEN attribute = 'attr4' THEN value END) AS attr4,

MAX(CASE WHEN attribute = 'attr5' THEN value END) AS attr5

FROM dbo.OpenSchema

GROUP BY objectid;

 

DROP TABLE dbo.OpenSchema

 

Nesse caso, foi usada apenas uma tabela. O que teria que ser feito no seu caso é um JOIN inicial entre as duas tabelas para listar os atributos e os valores.

 

[ ]s,

 

Gustavo

Answer 8

Jeferson não é tão simples mas eu faria algo parecido com isso.

 

Code Snippet

create table #Tabela1(

IdTabela1 int,

NomeCampo varchar(100))

create table #Tabela2(

IdTabela1 int,

IdTabela2 int,

valorcampo varchar(100),

IdPessoa int)

insert into #Tabela1 (IdTabela1, NomeCampo) Values(1, 'Telefone')

insert into #Tabela1 (IdTabela1, NomeCampo) Values(2, 'Endereco')

insert into #Tabela1 (IdTabela1, NomeCampo) Values(3, 'Cidade')

insert into #Tabela2 (IdTabela1, IdTabela2, ValorCampo, idPessoa )

Values (1,1, '2020-2020', 1)

insert into #Tabela2 (IdTabela1, IdTabela2, ValorCampo, idPessoa )

Values (1,2, '1515-1515', 2)

insert into #Tabela2 (IdTabela1, IdTabela2, ValorCampo, idPessoa )

Values (2,3, 'Rua B', 1)

insert into #Tabela2 (IdTabela1, IdTabela2, ValorCampo, idPessoa )

Values (3,4, 'Rio de Janeiro', 1)

insert into #Tabela2 (IdTabela1, IdTabela2, ValorCampo, idPessoa )

Values (3,5, 'Sao Paulo', 2)

create table #tmp (

idPessoa Int,

Telefone VarChar(100),

Endereco VarChar(100),

Cidade VarChar(100))

insert into #tmp(idpessoa)

select distinct idpessoa from #tabela2

update #tmp set Telefone = #tabela2.ValorCampo

from #tmp

inner join #tabela2

on #tmp.idPessoa = #tabela2.idPessoa

inner join #tabela1

on #tabela1.idtabela1 = #tabela2.idtabela1

where #tabela1.NomeCampo = 'Telefone'

update #tmp set Endereco = #tabela2.ValorCampo

from #tmp

inner join #tabela2

on #tmp.idPessoa = #tabela2.idPessoa

inner join #tabela1

on #tabela1.idtabela1 = #tabela2.idtabela1

where #tabela1.NomeCampo = 'Endereco'

update #tmp set Cidade = #tabela2.ValorCampo

from #tmp

inner join #tabela2

on #tmp.idPessoa = #tabela2.idPessoa

inner join #tabela1

on #tabela1.idtabela1 = #tabela2.idtabela1

where #tabela1.NomeCampo = 'Cidade'

select * from #tmp

GO

drop table #tmp

drop table #Tabela1

drop table #Tabela2

 

 

Answer 9

Boa Tarde,

 

Esse é o problema de soluções EAV. Elas sem dúvida aumentam a flexibilidade para adição de novos atributos e enxugam o armazenamento, mas em características como recuperação de dados são realmente "trágicas". Há dificuldades na indexação eficiente, bem como na elaboração das consultas. A solução do Fabiano é interessante, mas pode envolver praticamente a cópia da tabela inteira para uma temporária e isso não seria viável para grandes volumes. Acredito que qualquer outra solução também tenha algum ponto fraco.

 

Você poderia considerar a natureza semi-estruturada do XML para conseguir esse tipo de flexibilidade. O problema é que XML no SQL Server 2000 é pouco suportado.

 

[ ]s,

 

Gustavo

 

Answer 10

Gustavo concordo plenamente com você, trabalhar com esta modelagem não é nada simples e acabada sendo inviável para grande quantidade de dados. 

 

Answer 11

So para complementar com a minha opiniao.

 

1 - o exemplo que eu passei ajuda mais deve ser usado com muita prudencia ele nao vai aproveitar planos de execucao e na maioria dos casos nem indices, mais para resolver ajuda.

 

2 - a solucao de case alianhado na minha opniao tem o mesmo esfeito da pivot ou seja vc. precisa saber das colunas, nao gosto da perfornance da pivot afinal esse trabalho nao deve estar do lado transacional da aplicacao.

 

3 - alternativascross tab do reporting services ou crystal reports

 

4 - a melhor solucao entre todas e usar o analisys services criando um cubo paa essa situacao, nao existe solucao melhor que o uso de uma modelagem com mais dimencoes neste caso.

 

5 - outra alternativa seria vincular a consulta plan ( em linhas ) a um excel e fazer o excel criar a pivot.

 

espero ter colaborado.

 

Abs;