Sequential Dynamic T-SQL Pivot

Question

Sequential Dynamic T-SQL Pivot

Navigation

#1 by (1 votes)

4

Breaking the head here to create a proc using pivot for this transformation:

Is there a possibility?

* Using sql server 2008R2

EDIT:

    CREATE TABLE #Teste5
    (name VARCHAR(50),message VARCHAR(50),dtStart VARCHAR(50))
    GO
    INSERT INTO #Teste5 VALUES('Curso 1','Observ','2018-04-06')
    INSERT INTO #Teste5 VALUES('Curso 2','Observ','2018-04-06')
    INSERT INTO #Teste5 VALUES('Curso 1','Observ','2018-04-05')
    INSERT INTO #Teste5 VALUES('Curso 3','Observ','2018-04-02')
    INSERT INTO #Teste5 VALUES('Curso 4','Observ','2018-03-09')
    INSERT INTO #Teste5 VALUES('Curso 2','Observ','2018-03-09')
    INSERT INTO #Teste5 VALUES('Curso 1','Observ','2018-03-02')
    INSERT INTO #Teste5 VALUES('Curso 1','Observ','2018-01-05')
    INSERT INTO #Teste5 VALUES('Curso 4','Observ','2017-10-30')
    INSERT INTO #Teste5 VALUES('Curso 2','Observ','2017-09-25')
    GO
    select * from #Teste5

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)

    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
    + QUOTENAME(dtStart)
    FROM (SELECT DISTINCT dtStart FROM #Teste5) AS dtStart

    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
      N'SELECT name, ' + @ColumnName + '
        FROM #Teste5
        PIVOT(MAX(message) 
      FOR dtStart IN (' + @ColumnName + ')) AS PVTTable'
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

My table already exists. The create table is just for demonstrating

sql-server tsql

asked by anonymous 06.04.2018 / 22:12

1 answer

Hierarchy between CSS styles Closure Compiler JS - CSS Transform into a bulk process

score 1 · Accepted Answer

I believe this is not the best way, but I have decided to create a temporary table with the course table data by adding a column with a number sequentially generated i ++ using the following expression:

    ROW_NUMBER() OVER(PARTITION BY curso ORDER BY curso ASC) AS Row#.

After creating this table I used the generated index to set the columns in pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Row#) FROM (select distinct Row# from #TableTemp) AS Row#

SET @DynamicPivotQuery = N'SELECT name, ' + @ColumnName 
+ ' FROM #TableTempPIVOT(MAX(dtStart) FOR row# IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery