Pivot T-SQL Dynamic Sequential

Asked

Viewed 531 times

5

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

a busy cat

There is the 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. create table is just for better demonstration

sql-server tsql

  • Post the code you are working on, the base structure and what you really want to know, please.

  • I recommend you take a look before: https://answall.com/tour

  • I believe this is not the best way, but I decided to create a time table with the data of the course table by adding a column with a sequentially generated number i++ using the following expression: ROW_NUMBER() OVER(PARTITION BY ORDER BY ASC course) AS Row#. After creating this table I used the generated index to define the columns in the pivot

  • @Douglas If you’re still using the temporary table, you can find other ways to run the pivot in the "Alas & Pivots" article. Log in: https://portosql.wordpress.com/2019/05/04/pivot/

1 answer

2


I believe this is not the best way, but I decided to create a temporary table with the data of the course table by adding a column with a sequentially generated number 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 define the columns in the 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

Browser other questions tagged sql-server tsql

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