Jump to content

Trocar linhas por colunas


ferreira12

Recommended Posts

Boas, estou a fazer a seguinte query.

Select Marca,
case when XX = 'Vendas' then Valor else '' end Vendas,
case when XX = 'Devolucoes' then Valor else '' end Devolucoes
from (
select MArca,SUM(valor) as Valor, 'Vendas' as XX from tabela1
group by MarcaID
union
select Marca,SUM(valor) as Valor, 'Devolucoes' as XX from tabela2
)T

a seguinte query devolve a informação na seguinte posição:

MarcaID Vendas Devolucoes

-1 7 0,00

-1 0,00 3

No entanto eu desejava o seguinte:

MarcaID Vendas Devolucoes

-1 7 3

Como consigo ultrapassar este problema?

Edited by ferreira12
Link to comment
Share on other sites

Qual a versão do SQL Server que estás a usar?

Using SQL Server Management Studio

To view or change the compatibility level of a database


  1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name.

  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. Right-click the database, and then click Properties.
    The Database Properties dialog box opens.

  4. In the Select a page pane, click Options.
    The current compatibility level is displayed in the Compatibility level list box.

  5. To change the compatibility level, select a different option from the list. The choices are SQL Server 2008 (100), SQL Server 2012 (110), or SQL Server 2014 (120).

Mais info aqui:

https://msdn.microsoft.com/en-us/library/bb933794.aspx

Pedro Martins

Sharing is Knowledge!

http://www.linkedin.com/in/rechousa

Link to comment
Share on other sites

Bem, podes sempre somar os valores das linhas. Serve?

SELECT
   Marca, SUM(Vendas) AS Vendas, SUM(Devolucoes) AS Devolucoes
FROM
(
   Select Marca,
   case when XX = 'Vendas' then Valor else '' end Vendas,
   case when XX = 'Devolucoes' then Valor else '' end Devolucoes
   from (
   select MArca,SUM(valor) as Valor, 'Vendas' as XX from tabela1
   group by MarcaID
   union
   select Marca,SUM(valor) as Valor, 'Devolucoes' as XX from tabela2
   )T
) T
   GROUP BY Marca;

Pedro Martins

Sharing is Knowledge!

http://www.linkedin.com/in/rechousa

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.