Criar View

Question

Prezados veja se pode me ajudar, Preciso criar uma View para SQL abaixo.

SELECT 
        YEAR = YEAR(dataemissao),
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
             END), 1), '') AS 'JAN',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
             END), 1), '') AS 'FEB',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
             END), 1), '') AS 'MAR',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
             END), 1), '') AS 'APR',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
             END), 1), '') AS 'MAY',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
             END), 1), '') AS 'JUN',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
 END), 1), '') AS 'JUL',        
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
             END), 1), '') AS 'AUG',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
             END), 1), '') AS 'SEP',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
             END), 1), '') AS 'OCT',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
             END), 1), '') AS 'NOV',
         COALESCE(CONVERT(VARCHAR,SUM(CASE
               WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
             END), 1), '') AS 'DEC' ,
   TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)   
FROM    tmov
WHERE  TMOV.CODTMV = '2.2.01'

GROUP BY  YEAR(dataemissao)
ORDER BY YEAR(dataemissao)
GO

Answer 1

Ronnie,

Basta substituir o NomeDaView, pelo nome que voce quiser.

CREATE VIEW dbo.NomeDaVida
AS
SELECT  
        YEAR = YEAR(dataemissao),
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
             END), 1), '') AS 'JAN', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
             END), 1), '') AS 'FEB', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
             END), 1), '') AS 'MAR', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
             END), 1), '') AS 'APR', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
             END), 1), '') AS 'MAY', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
             END), 1), '') AS 'JUN', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
 END), 1), '') AS 'JUL',         
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
             END), 1), '') AS 'AUG', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
             END), 1), '') AS 'SEP', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
             END), 1), '') AS 'OCT', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
             END), 1), '') AS 'NOV', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
             END), 1), '') AS 'DEC' ,
   TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)    
FROM    tmov
WHERE  TMOV.CODTMV = '2.2.01'
GROUP BY  YEAR(dataemissao) 

---

Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
ITIL V3 Foundation
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Email: [e-mail]

Answer 2

Ronnie Von, tente assim:

Create View [Nome da view] as

SELECT  
        YEAR = YEAR(dataemissao),
          COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
              END), 1), '') AS 'JAN', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
              END), 1), '') AS 'FEB', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
              END), 1), '') AS 'MAR', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
              END), 1), '') AS 'APR', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
              END), 1), '') AS 'MAY', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
              END), 1), '') AS 'JUN', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
  END), 1), '') AS 'JUL',         
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
              END), 1), '') AS 'AUG', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
              END), 1), '') AS 'SEP', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
              END), 1), '') AS 'OCT', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
              END), 1), '') AS 'NOV', 
         COALESCE(CONVERT(VARCHAR,SUM(CASE 
               WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
              END), 1), '') AS 'DEC' ,
    TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)   
 FROM    tmov
 WHERE  TMOV.CODTMV = '2.2.01'
 
GROUP BY  YEAR(dataemissao) 

---

Alexandre Matayosi Conde Mauricio.

Answer 3

valeu galera OK.