Dynamic PIOVTing

Overview:

                Pivot gives you the options to make rows data in to columns and this example gives you the way to create dynamic pivoting. You can see in the query that locations are assigned to a variable and the same has been used in the PIVOT query. So, rows data that i.e AUS, UAE, UK and USA are taken into the variable.

 

Base table:

Query:

            DECLARE     @Query      VARCHAR(200);

DECLARE     @Columns    VARCHAR(200);

SELECT      @Columns=COALESCE(@Columns+',','')+'['+a.Location+']'

FROM        (     SELECT DISTINCT Location FROM DynamicPivot)a

SET         @Query =    'SELECT     Name,'+@Columns+'

                        FROM  (     SELECT      Name, Location, Quantity

                                    FROM  DynamicPivot

                              ) a

                        PIVOT(SUM(Quantity) FOR Location IN ('+@Columns+')) pvt'

EXEC(@Query);

Result: