none
Desafio: Poucas linhas em muitas colunas, o que usar? Pivot, Tabela temporária ou outro método? RRS feed

  • Pergunta

  • No SLQ Server 2008, estou tentando tranformar 2 colunas da Tabela-I:

    ID NOME
    1 ac
    1 ab
    1 aa
    2 bb
    2 ba
    2 bc
    3 ca
    3 cb
    3 cc
    ... ...

    Para 4 colunas na Tabela-II:

    ID NOME-I NOME-II NOME-II
    1 aa ab ac
    2 ba bb bc
    3 ca cb cc
    ... ... ... ...

    Objetivo:

    1. Uma VIEW como resultado (para que eu possa usá-la em Procedures, por isso uma tabela temporária não serve, acredito eu...);
    2. O resultado deve estar em sequência na linha (em ordem alfabética como na Tabela-II);
    3. Os IDs sempre serão de 3 em 3, mas sempre novos serão adicionados (atualmente são 1254).

    Já pensei em várias formas e olhei em vários fóruns, o que achei muito é fazer uma tranformação inversa pelo unpivot, pelo pivot com sum, count e coisas do gênero (o que não é o caso), mas nenhuma deste tipo.

    Se alguém souber, poderia compartilhar a solução deste problema.

    Grato

    • Editado LightningBolt terça-feira, 15 de fevereiro de 2011 12:50
    terça-feira, 15 de fevereiro de 2011 01:59

Respostas

  • LightningBolt, boa noite

    segue dois exemplo, 1 com pivot simples e outro dinamico.

     

    -- Cria a tabela temporária
    CREATE TABLE #t (ID INT, Nome VARCHAR(50))
    
    -- Populando a tabela temporária
    INSERT INTO #t VALUES(1,'ac')
    INSERT INTO #t VALUES(1,'ab')
    INSERT INTO #t VALUES(1,'aa')
    INSERT INTO #t VALUES(2,'bb')
    INSERT INTO #t VALUES(2,'ba')
    INSERT INTO #t VALUES(2,'bc')
    INSERT INTO #t VALUES(3,'ca')
    INSERT INTO #t VALUES(3,'cb')
    INSERT INTO #t VALUES(3,'cc')
    
    -- Select final com pivot
    SELECT 
    	B.ID, B.[1], B.[2], B.[3]
    FROM 
    (
    	SELECT 
    		ID, Nome, 
    		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Nome ASC) as IdGrp
    	 FROM	
    		#T
    )AS A
    PIVOT (
        MAX(Nome)
        FOR IdGrp IN ([1], [2], [3])
       ) AS B
    
    
    
    -------------------------------
    -- ou Pivot Dinâmico
    
    
    -- criando outra tabela temporária com base na tabela MATRIZ 
    SELECT 	ID, Nome,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Nome ASC) as IdGrp 
    INTO #Dinamico	
    FROM #T
    
    -- declarando duas variáveis
    DECLARE @Colunas VARCHAR(MAX), 
        @SQL   NVARCHAR(MAX)
    
    -- montando a coluna do pivot dinamicamente
    SET @Colunas = STUFF(
    					 (SELECT N', '+ QUOTENAME(CAST(IdGrp AS VARCHAR(MAX))) 
    	         FROM #Dinamico GROUP BY IdGrp ORDER BY IdGrp ASC
    	         FOR XML PATH(''))
    	         ,1,2,'')
    
    -- rodando o select final com pivot
    SET @SQL = N''
    SET @SQL = @SQL + N' SELECT Id, ' + @Colunas
    SET @SQL = @SQL + N' FROM #Dinamico'
    SET @SQL = @SQL + N' PIVOT (MAX(Nome) FOR IdGrp IN ('+@Colunas+N')) AS Z'
    EXEC SP_EXECUTESQL @SQL
    
    -- excluindo as duas tabelas temporárias
    DROP TABLE #T
    DROP TABLE #Dinamico
    
    


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Marcado como Resposta LightningBolt quarta-feira, 2 de março de 2011 02:40
    quarta-feira, 2 de março de 2011 01:56

Todas as Respostas

  • LightningBolt

    No seu caso pivot ou unpivot é dificil pq vc precisa ter um caompo para usar uma função agregada (count, sum...)

    um modo é com CTE e XML

    DECLARE @tbTeste TABLE (ID INT, nome VARCHAR(50))
    
    INSERT INTO @tbTeste VALUES(1,'ac')
    INSERT INTO @tbTeste VALUES(1,'ab')
    INSERT INTO @tbTeste VALUES(1,'aa')
    INSERT INTO @tbTeste VALUES(2,'bb')
    INSERT INTO @tbTeste VALUES(2,'ba')
    INSERT INTO @tbTeste VALUES(2,'bc')
    INSERT INTO @tbTeste VALUES(3,'ca')
    INSERT INTO @tbTeste VALUES(3,'cb')
    INSERT INTO @tbTeste VALUES(3,'cc')
    ;with cte_dados
    as(
    SELECT a.ID,
      STUFF((SELECT ' / ' + b.nome AS "text()" 
         FROM @tbTeste b
         WHERE a.ID = b.ID --and a.NOME=b.nome
         FOR XML PATH('')),1,3, '') AS nome
     FROM @tbTeste AS a
     GROUP BY a.ID
    )
    
    SELECT ID,
      LTRIM(RTRIM(TabNomeI.ColXML.value('@Ind', 'VarChar(80)'))) AS NomeI,
      LTRIM(RTRIM(TabNomeII.ColXML.value('@Ind', 'VarChar(80)'))) AS NomeII,
      LTRIM(RTRIM(TabNomeIII.ColXML.value('@Ind', 'VarChar(80)'))) AS NomeIII
     FROM (SELECT *,
        CONVERT(XML, '<Teste Ind="' + Replace(nome, '/',
                 '"/><Teste Ind="') + '"/>') AS ColXML
       FROM cte_dados) AS Tab
    CROSS APPLY Tab.ColXML.nodes('/Teste[1]') As TabNomeI (ColXML)
    CROSS APPLY Tab.ColXML.nodes('/Teste[2]') As TabNomeII (ColXML)
    CROSS APPLY Tab.ColXML.nodes('/Teste[3]') As TabNomeIII (ColXML)
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    terça-feira, 15 de fevereiro de 2011 12:37
  • Marcelo, obrigado pelo tempo despendido para ver este caso, só que eu precisaria usar o resultado em procedures. Penso que o melhor seria fazer a Tabela-II como uma View e não como um xml.

    Você sabe se existe alguma função agregada ou algo parecido com uma função "distinct" para empregar no ID da tabela usando pivot?

    terça-feira, 15 de fevereiro de 2011 13:01
  • LightningBolt Medalhas de usuárioMedalhas de usuárioMedalhas de usuárioMedalhas de usuárioMedalhas de usuário

    Neste cenário vc pode usar em uma proc!  vc pode criar uma outra CTE e usar em seu comando...

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    terça-feira, 15 de fevereiro de 2011 15:02
  • LightningBolt, boa noite

    segue dois exemplo, 1 com pivot simples e outro dinamico.

     

    -- Cria a tabela temporária
    CREATE TABLE #t (ID INT, Nome VARCHAR(50))
    
    -- Populando a tabela temporária
    INSERT INTO #t VALUES(1,'ac')
    INSERT INTO #t VALUES(1,'ab')
    INSERT INTO #t VALUES(1,'aa')
    INSERT INTO #t VALUES(2,'bb')
    INSERT INTO #t VALUES(2,'ba')
    INSERT INTO #t VALUES(2,'bc')
    INSERT INTO #t VALUES(3,'ca')
    INSERT INTO #t VALUES(3,'cb')
    INSERT INTO #t VALUES(3,'cc')
    
    -- Select final com pivot
    SELECT 
    	B.ID, B.[1], B.[2], B.[3]
    FROM 
    (
    	SELECT 
    		ID, Nome, 
    		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Nome ASC) as IdGrp
    	 FROM	
    		#T
    )AS A
    PIVOT (
        MAX(Nome)
        FOR IdGrp IN ([1], [2], [3])
       ) AS B
    
    
    
    -------------------------------
    -- ou Pivot Dinâmico
    
    
    -- criando outra tabela temporária com base na tabela MATRIZ 
    SELECT 	ID, Nome,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Nome ASC) as IdGrp 
    INTO #Dinamico	
    FROM #T
    
    -- declarando duas variáveis
    DECLARE @Colunas VARCHAR(MAX), 
        @SQL   NVARCHAR(MAX)
    
    -- montando a coluna do pivot dinamicamente
    SET @Colunas = STUFF(
    					 (SELECT N', '+ QUOTENAME(CAST(IdGrp AS VARCHAR(MAX))) 
    	         FROM #Dinamico GROUP BY IdGrp ORDER BY IdGrp ASC
    	         FOR XML PATH(''))
    	         ,1,2,'')
    
    -- rodando o select final com pivot
    SET @SQL = N''
    SET @SQL = @SQL + N' SELECT Id, ' + @Colunas
    SET @SQL = @SQL + N' FROM #Dinamico'
    SET @SQL = @SQL + N' PIVOT (MAX(Nome) FOR IdGrp IN ('+@Colunas+N')) AS Z'
    EXEC SP_EXECUTESQL @SQL
    
    -- excluindo as duas tabelas temporárias
    DROP TABLE #T
    DROP TABLE #Dinamico
    
    


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Marcado como Resposta LightningBolt quarta-feira, 2 de março de 2011 02:40
    quarta-feira, 2 de março de 2011 01:56
  • Perfeito! Era bem disso que eu precisava!
    Não conhecia a linha mágica:
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Nome ASC) as IdGrp
    
    Transformei o pivot simples numa view que puxa os dados de outra view.

    Valeu mesmo!

    quarta-feira, 2 de março de 2011 02:38
  • LightningBolt,  valeu pelo retorno.
    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    quarta-feira, 2 de março de 2011 11:18