SQL With Joins and Pivot

Question

I spent more than 6 hours today trying to figure this SQL solution to no avail. Honestly, I figured it out after some searches that I had to use the PIVOT instruction (Never saw it before) to get what I´m after, and since then I´ve trying to figure out this command when used with join commands. I´m not that smart per see.

This is what I´m trying to do:

I have these tables (I´m showing the ones I believe are relevant for this question since there a few other INNER JOINS referencing other tables):

---

---

And this query:

SELECT [Easytool_data].[dbo].[estoque].[id] AS ID
      ,[Easytool_data].[dbo].[estoque].[barras] AS PartNumber
      ,[Easytool_data].[dbo].[estoque].[codigo] AS Codigo
      ,[Easytool_data].[dbo].[estoque].[descricao] AS Descricao
      ,[Easytool_data].[dbo].[estoque].[um] AS Unidade
      ,[Easytool_data].[dbo].[familias].[Descricao] AS Familia
      ,[Easytool_data].[dbo].[estoque].[fabricante] AS Fabricante
      ,[Easytool_data].[dbo].[estoque].[modelo] AS Modelo
      ,[Easytool_data].[dbo].[estoque].[armazenagem] AS Local
      ,[Easytool_data].[dbo].[status].[Descricao] AS Situacao
      ,[Easytool_data].[dbo].[estoque].[custo] AS Preco
      ,[Easytool_data].[dbo].[estoque].[sd_atual] AS Saldo
      ,[Easytool_data].[dbo].[local_estoque].[descricao] AS Estoque
      ,[Easytool_data].[dbo].[local_estocado].[quantidade] AS Quantidade

FROM [Easytool_data].[dbo].[estoque] 

INNER JOIN [Easytool_data].[dbo].[familias]       
ON [Easytool_data].[dbo].[estoque].[id_familia] = [Easytool_data].[dbo].[familias].[Id]
INNER JOIN [Easytool_data].[dbo].[status]
ON [Easytool_data].[dbo].[estoque].[id_status] = [Easytool_data].[dbo].[status].[Id]
INNER JOIN [Easytool_data].[dbo].[local_estocado]
ON [Easytool_data].[dbo].[estoque].[id] = [Easytool_data].[dbo].[local_estocado].[id_estoque]
INNER JOIN [Easytool_data].[dbo].[local_estoque]
ON [Easytool_data].[dbo].[local_estocado].[id_local_estoque] = [Easytool_data].[dbo].[local_estoque].[id]

WHERE  [Easytool_data].[dbo].[estoque].[id] = 3954

Which returns this:

ID     | PartNumber | Codigo    | Descricao   | Unidade | Familia   | Fabricante | Modelo | Local     | Situacao | Preco | Saldo | Estoque     | Quantidade
----------------------------------------------------------------------------------------------------------------------------------------------------
3954   | OSPÇ0024   |  OSPÇ0024 | PINÇA ER 25 | UNIDADE | PINÇA     | SANDVIK    | NULL   | PI 01-D-2 | ATIVO    | 25    | 2     | CENTRAL     | 1
----------------------------------------------------------------------------------------------------------------------------------------------------
3954   | OSPÇ0024   |  OSPÇ0024 | PINÇA ER 25 | UNIDADE | PINÇA     | SANDVIK    | NULL   | PI 01-D-2 | ATIVO    | 25    | 2     | ROTATIVO 0  | 0
----------------------------------------------------------------------------------------------------------------------------------------------------
3954   | OSPÇ0024   |  OSPÇ0024 | PINÇA ER 25 | UNIDADE | PINÇA     | SANDVIK    | NULL   | PI 01-D-2 | ATIVO    | 25    | 2     | REFORMA  0  | 0
----------------------------------------------------------------------------------------------------------------------------------------------------
3954   | OSPÇ0024   |  OSPÇ0024 | PINÇA ER 25 | UNIDADE | PINÇA     | SANDVIK    | NULL   | PI 01-D-2 | ATIVO    | 25    | 2     | EMPRÉSTIMO  | 1

I want it to return this:

ID     | PartNumber |   Codigo  | Descricao   | Unidade |   Familia | Fabricante | Modelo | Local     | Situacao | Preco | Saldo | Estoque | Quantidade | Estoque    | Quantidade | Estoque   | Quantidade | Estoque    | Quantidade
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3954   |OSPÇ0024   |  OSPÇ0024  | PINÇA ER 25 | UNIDADE | PINÇA     | SANDVIK    | NULL   | PI 01-D-2 | ATIVO    | 25    | 2     | CENTRAL | 1          | ROTATIVO 0 | 0          | REFORMA 0 | 0          | EMPRÉSTIMO | 1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I´ve learned very recently that PIVOT can be used to accomplish this, but I´m totally lost about how to use it with several INNER JOIN commands like the one in my query. I´ve tried it in several different ways and always get an error.

Can anybody give me an example about how I could achieve what I´m after using a query similar to mine?

I´ve searched the board and all examples I found were way too complex or way to simple compared to my query. I´m not lazy and I´m willing to put my best efforts in learning this, but it seems out of reach for now.

If someone could shed some light here I´d be very thankful,

Thank you Daniel

Answer 1

Pivoting horizontally using just SQL is usually about using a descriptive, previously vertical column; the same you use for the CASE expression, as a column name. Like so:

WITH initial_query_output (
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo
, Estoque
, Quantidade
) AS (
          SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'CENTRAL'    ,1
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'ROTATIVO 0' ,0
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'REFORMA  0' ,0
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'EMPRÉSTIMO' ,1
)
SELECT
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo
, SUM(CASE Estoque WHEN 'CENTRAL'    THEN Quantidade END) AS "CENTRAL"   
, SUM(CASE Estoque WHEN 'ROTATIVO 0' THEN Quantidade END) AS "ROTATIVO 0"
, SUM(CASE Estoque WHEN 'REFORMA  0' THEN Quantidade END) AS "REFORMA  0"
, SUM(CASE Estoque WHEN 'EMPRÉSTIMO' THEN Quantidade END) AS "EMPRÉSTIMO"
FROM initial_query_output
GROUP BY
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo
;

ID   |PartNumber|Codigo|Descricao |Unidade|Familia|Fabricante|Modelo|Local   |Situacao|Preco|Saldo|CENTRAL|ROTATIVO 0|REFORMA  0|EMPRÉSTIMO
3,954|OSPÇ002   |OSPÇ00|PINÇA ER 2|UNIDAD |PINÇ   |SANDVI    |NUL   |PI 01-D-|ATIV    |   25|    2|      1|         0|         0|         1

Answer 2

This should work if you replace the fullselect, Common Table Expression that I called initial_query_output by your query above:

WITH initial_query_output (
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo
, Estoque
, Quantidade
) AS (
-- replace with your initial query from here
          SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'CENTRAL'    ,1
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'ROTATIVO 0' ,0
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'REFORMA  0' ,0
UNION ALL SELECT 3954,'OSPÇ002','OSPÇ00','PINÇA ER 2','UNIDAD','PINÇ','SANDVI','NUL','PI 01-D-','ATIV',25,2,'EMPRÉSTIMO' ,1
-- replace with your initial query until here
)
SELECT
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo
, MAX(CASE Estoque WHEN 'CENTRAL'    THEN Estoque    END) AS Estoque1
, MAX(CASE Estoque WHEN 'CENTRAL'    THEN Quantidade END) AS Quantidade1
, MAX(CASE Estoque WHEN 'ROTATIVO 0' THEN Estoque    END) AS Estoque2
, MAX(CASE Estoque WHEN 'ROTATIVO 0' THEN Quantidade END) AS Quantidade2
, MAX(CASE Estoque WHEN 'REFORMA  0' THEN Estoque    END) AS Estoque3
, MAX(CASE Estoque WHEN 'REFORMA  0' THEN Quantidade END) AS Quantidade3
, MAX(CASE Estoque WHEN 'EMPRÉSTIMO' THEN Estoque    END) AS Estoque4
, MAX(CASE Estoque WHEN 'EMPRÉSTIMO' THEN Quantidade END) AS Quantidade4
FROM initial_query_output
GROUP BY
  ID
, PartNumber
, Codigo
, Descricao
, Unidade
, Familia
, Fabricante
, Modelo
, Local
, Situacao
, Preco
, Saldo

ID   |PartNumber|Codigo|Descricao |Unidade|Familia|Fabricante|Modelo|Local   |Situacao|Preco|Saldo|Estoque1|Quantidade1|Estoque2  |Quantidade2|Estoque3  |Quantidade3|Estoque4  |Quantidade4
3,954|OSPÇ002   |OSPÇ00|PINÇA ER 2|UNIDAD |PINÇ   |SANDVI    |NUL   |PI 01-D-|ATIV    |   25|    2|CENTRAL |          1|ROTATIVO 0|          0|REFORMA  0|          0|EMPRÉSTIMO|          1

Answer 3

I assume the first subquery returns one row only by each [Easytool_data].[dbo].[estoque].[id], If not you must GRUP BY every field in the subquery.

WITH Cmd As
(
    SELECT [Easytool_data].[dbo].[estoque].[id] AS ID
          ,[Easytool_data].[dbo].[estoque].[barras] AS PartNumber
          ,[Easytool_data].[dbo].[estoque].[codigo] AS Codigo
          ,[Easytool_data].[dbo].[estoque].[descricao] AS Descricao
          ,[Easytool_data].[dbo].[estoque].[um] AS Unidade
          ,[Easytool_data].[dbo].[familias].[Descricao] AS Familia
          ,[Easytool_data].[dbo].[estoque].[fabricante] AS Fabricante
          ,[Easytool_data].[dbo].[estoque].[modelo] AS Modelo
          ,[Easytool_data].[dbo].[estoque].[armazenagem] AS Local
          ,[Easytool_data].[dbo].[status].[Descricao] AS Situacao
          ,[Easytool_data].[dbo].[estoque].[custo] AS Preco
          ,[Easytool_data].[dbo].[estoque].[sd_atual] AS Saldo
    FROM [Easytool_data].[dbo].[estoque] 
    INNER JOIN [Easytool_data].[dbo].[familias]       
    ON [Easytool_data].[dbo].[estoque].[id_familia] = [Easytool_data].[dbo].[familias].[Id]
    INNER JOIN [Easytool_data].[dbo].[status]
    ON [Easytool_data].[dbo].[estoque].[id_status] = [Easytool_data].[dbo].[status].[Id]
    WHERE  [Easytool_data].[dbo].[estoque].[id] = 3954
)
SELECT ID, PartNumber, Codigo, Descricao, Unidade, Familia, Fabricante, Modelo
       , Local, Situacao, Preco, Saldo,
       CASE WHEN [Easytool_data].[dbo].[local_estoque].[descricao] = 'CENTRAL' 
            THEN [Easytool_data].[dbo].[local_estocado].[quantidade]
            ELSE 0 END AS CENTRAL, 
       CASE WHEN [Easytool_data].[dbo].[local_estoque].[descricao] = 'ROTATIVO' 
            THEN [Easytool_data].[dbo].[local_estocado].[quantidade]
            ELSE 0 END AS ROTATIVO, 
       CASE WHEN [Easytool_data].[dbo].[local_estoque].[descricao] = 'REFORMA' 
            THEN [Easytool_data].[dbo].[local_estocado].[quantidade]
            ELSE 0 END AS REFORMA, 
       CASE WHEN [Easytool_data].[dbo].[local_estoque].[descricao] = 'EMPRÉSTIMO' 
            THEN [Easytool_data].[dbo].[local_estocado].[quantidade]
            ELSE 0 END AS EMPReSTIMO 
FROM Cmd
    INNER JOIN [Easytool_data].[dbo].[local_estoque]
    ON Cmd.[id_local_estoque] = [Easytool_data].[dbo].[local_estoque].[id];
    INNER JOIN [Easytool_data].[dbo].[local_estocado]
    ON [Easytool_data].[dbo].[local_estocado].[id_estoque] = Cmd.Id;