/SQL/SQL Server dynamic pivot unknown number of columns

SQL Server dynamic pivot unknown number of columns

-   DECLARE @v_query  VARCHAR(8000) -- main query
    DECLARE @v_columns VARCHAR(8000) -- columns

    SET @v_columns =''

    -- Get string columns
    SELECT @v_columns += '[' + CONVERT(VARCHAR, Item) +'],' FROM (SELECT DISTINCT Item FROM #temp2) AS temp

    -- Delete the last comma
    SET @v_columns = LEFT(@v_columns,LEN(@v_columns)-1)


    -- Main query

    SET @v_query = 'SELECT Name, ' + @v_columns +' FROM
                    (
                        SELECT Name, Item FROM #temp2
                    ) T             
                    PIVOT
                    (
                        Count(Item)
                        FOR Item IN ('+ @v_columns +')
                    ) PVT'
    EXEC (@v_query)

Source: stackoverflow.com

Similar codes

16 votes171 views3 answers
MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
SQL
9 votes142 views3 answers
Show table columns mysql command line
SQL
1 vote144 views2 answers
Grab part of a string sql
SQL
6 votes157 views1 answer
Postgresql add enum value
SQL
0 vote166 views1 answer
Google cloud sql postgres url example
SQL
0 vote143 views1 answer
Install mysql 8 linux
SQL
7 votes144 views1 answer
Append column sql
SQL
3 votes160 views1 answer
Select sequence oracle
SQL
-1 vote152 views8 answers
Create Trigger in phpMyAdmin
SQL
1 vote177 views1 answer
Hangfire clear all jobs
SQL