I cannot place a subquery inside the IN in the PIVOT

Asked

Viewed 652 times

6

Below is the code for table creation and insertion of some data for test.

CREATE TABLE [dbo].[tb_empresas]( [data] [date] NULL, [nome] [varchar](100) NULL, [valor] [decimal](18, 2) NULL ) ON [PRIMARY]

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-01-01',103), 'EMPRESA1', 100)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-02-02',103), 'EMPRESA2', 200)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-03-03',103), 'EMPRESA3', 300)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-04-04',103), 'EMPRESA4', 400)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-05-05',103), 'EMPRESA5', 500)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-06-06',103), 'EMPRESA6', 600)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-07-07',103), 'EMPRESA7', 700)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-08-08',103), 'EMPRESA8', 800)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-09-09',103), 'EMPRESA9', 900)

INSERT INTO tb_empresas (data, nome, valor)
VALUES (convert(datetime,'2012-10-10',103), 'EMPRESA10', 1000)

The PIVOT working is like this:

SELECT * FROM
    (SELECT DATEPART(MONTH, DATA) AS MES, NOME, VALOR FROM tb_empresas)
AS DADOS_HORIZONTAIS 
PIVOT
    ( 
        MAX(VALOR) 
        FOR NOME 
        IN([EMPRESA1],[EMPRESA2],[EMPRESA3])
    ) AS PivotTable

But where is EMPRESA1, EMPRESA2 and EMPRESA3, would need to bring in a select all company names for example:

IN(SELECT nome FROM tb_empresas)
sql sql-server pivot

  • gives a look at this link to see if it helps: http://www.devmedia.com.br/pivot-no-sql-server-invertendo-linhas-e-colunas-em-um-exemplo-real/28318

2 answers

3

I knew I’d seen something, I just couldn’t remember where. Devmedia has the same example:

CREATE PROCEDURE [dbo].[OBTER_COLUNAS_DINAMICAMENTE] 
    AS 
    SET NOCOUNT ON 
    DECLARE @COLUNAS VARCHAR(MAX) 
SET @COLUNAS = '' 
SELECT @COLUNAS = COALESCE(@COLUNAS + '[' + (CAST(NOME AS NVARCHAR(255))) + '],','') 
FROM (SELECT DISTINCT NOME FROM tb_empresas) AS DADOS_HORIZONTAIS 

SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1) 

DECLARE @SQLSTRING NVARCHAR(500); 
SET @SQLSTRING = N' SELECT * FROM (SELECT DATEPART(MONTH, DATA) AS MES, NOME, VALOR FROM tb_empresas) AS DADOS_HORIZONTAIS 
PIVOT( MAX(VALOR) FOR NOME IN('+@COLUNAS+')) AS PivotTable;' 

EXECUTE SP_EXECUTESQL @SQLSTRING

You can use this precedent to list all companies and pivot.

0

According to documentation from Technet only columns are allowed. It will not be possible to place a dynamic selection there.

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

   ... [last pivoted column])

) AS <alias for the pivot table>

Browser other questions tagged sql sql-server pivot

You are not signed in. Login or sign up in order to post.