SQL Server dynamic row to column

Sometimes you will need the function of SQL result set row to column

For SQL row-to-column (PIVOT) and column-changing (UNPIVOT), refer to Https://www.cnblogs.com/linJie1930906722/p/6036714.html

But sometimes it takes a dynamic column and the data part of the presentation is a string using the method above does not make the

For example, to turn the top half of the picture below into the lower half.

SQL as follows

But because exec executes the SELECT INTO No, the insert select does the trouble only with the last return result.

If the concurrency query is not serious, you can insert a table that specifically holds the data, not the temporary table.

or build a temporary table and SQL dynamic Insert column. Never tried this way.

--the underlying data is the data that requires row-to-columnCREATE TABLE#人员答题数据 (Name of personNVARCHAR( -)NULL, title nameNVARCHAR( -)NULL, the answer to the resultNVARCHAR( -)NULL    )INSERT   into#人员答题数据 (name of person, title, answer result)SELECT  'Zhang San' ,                'Topic 1' ,                'Zhang San topic 1 Answers'        UNION        SELECT  'Zhang San' ,                'Topic 2' ,                'Zhang San topic 2 answers'        UNION        SELECT  'Zhang San' ,                'Topic 3' ,                'Zhang San topic 3 answers'        UNION        SELECT  'John Doe' ,                'Topic 1' ,                'John Doe topic 1 Answers'        UNION        SELECT  'John Doe' ,                'Topic 2' ,                'John Doe Topic 2 answers'        UNION        SELECT  'John Doe' ,                'Topic 3' ,                'John Doe Topic 3 answers'SELECT *  from#人员答题数据ORDER  byName of person, title--Stitching The final Select column data with the column headings in the underlying data is all complete and can be distinct from the underlying dataCREATE  TABLE#行数据中的需要转成列标题的数据 (title nameNVARCHAR( $)NULL )INSERT   into#行数据中的需要转成列标题的数据SELECT DISTINCTTopic name from#人员答题数据DECLARE @sql Stitching Final column NVARCHAR(MAX)= "'              SET @sql Stitching Final column = "'              SELECT  @sql Stitching Final column+= 'ISNULL (MAX (case topic name when" " +Topic name+ " "Then answer the result end)," '") as [' +Topic name+ '],' from#行数据中的需要转成列标题的数据--one more, get rid of it.IF(@sql Stitching Final column != "' )    BEGIN                  SET @sql Stitching Final column = @sql Stitching Final column + '$'                  SET @sql Stitching Final column = REPLACE(@sql Stitching Final column,',$',"')              END              DECLARE @sql NVARCHAR(MAX)SET @sql = 'SELECT person Name,' + @sql Stitching Final column                       SET @sql += 'From #人员答题数据 GROUP by person name'  EXEC(@sql)              PRINT(@sql )     DROP TABLE#人员答题数据, #行数据中的需要转成列标题的数据

View Code

SQL Server dynamic row to column