I can not put a subquery inside the IN in PIVOT

Question

I can not put a subquery inside the IN in PIVOT

Navigation

#1 by (3 votes)
#2 by (0 votes)

6

Below is the code for creating the table and inserting some data for testing.

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 looks 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 COMPANY1, COMPANY2 and COMPANY3, you would need to bring in a select all company names eg:

IN(SELECT nome FROM tb_empresas)

sql sql-server pivot

asked by anonymous 10.03.2014 / 15:29

2 answers

Is it possible to use timestamp without having to convert to String in Oracle? How to add the fadeIn effect in this image transition code

score 3 · Answer 1

I knew I had seen something, I just could not 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 procedure to list all companies and make the pivot.

score 0 · Answer 2

According to the technet documentation, only columns are allowed . You will not be able to put 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>