none
Passar dados de linha para coluna RRS feed

  • Pergunta

  • 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

     

    quinta-feira, 3 de abril de 2008 16:46

Respostas

  • 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;

    sexta-feira, 4 de abril de 2008 10:26

Todas as Respostas

  • 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

    quinta-feira, 3 de abril de 2008 16:50
  •  

    A versão do SQL Server é 2000, mas tb tem que funcionar para 2005.
    quinta-feira, 3 de abril de 2008 16:54
  • 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

     

     

    quinta-feira, 3 de abril de 2008 17:06
  • 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.

     

    quinta-feira, 3 de abril de 2008 17:25
  • Entendi,

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

     

     

    Abs.

     

     

    quinta-feira, 3 de abril de 2008 17:28
  • 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

     

     

    quinta-feira, 3 de abril de 2008 17:31
  • 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

    quinta-feira, 3 de abril de 2008 18:07
  • 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

     

     

    quinta-feira, 3 de abril de 2008 19:25
  • 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

     

    quinta-feira, 3 de abril de 2008 19:39
  • Gustavo concordo plenamente com você, trabalhar com esta modelagem não é nada simples e acabada sendo inviável para grande quantidade de dados. 

     

    quinta-feira, 3 de abril de 2008 20:18
  • 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;

    sexta-feira, 4 de abril de 2008 10:26