We had learned and understood the Pivot and how to transpose the rows into the columns in the previous post.
The previous example will be helpful only when we know the values in a particular column.
But what if the values are not known and not predictable. This seems to be tricky... :D
We can do it by using the dynamic sql query.
We will continue with the previous example.
--Declare the variables to store the column names and the select query
Declare @Column_Names nvarchar(max)
Declare @Query nvarchar(max)
--Saving the distinct values in Subject Column as comma separated string
Select @Column_Names = Coalesce(@Column_Names + ', ', '') + QUOTENAME(Subject)
from
(Select distinct Subject from Student) columnString
Select @Query = 'Select Name, ' + @Column_Names + ' from
(Select Name, Subject, Marks from Student) as table1
PIVOT
(
SUM(Marks) for Subject IN (' + @Column_Names + ')
) AS table2
Order By table2.Name '
EXEC sp_executesql @Query
We had used two SQL Server functions in the above example
- Coalesce - That will return the first no nullable value from the list of variables passed.
Ex=> Coalesce(null,null,'abc') => It will return 'abc'
- QUOTENAME - It is used to start and end the value by sqare brackets ([]) if no delimiters is passed.
Ex => QUOTENAME('ABC') => [ABC]
- EXEC - It is used to execute a stored procedure and sp_executesql is a stored procedure that will takes a string as input and execute the query.
The above example will produce the following output
#Happy coding...
- Please give your suggestion if you find anything incorrect. contact us at team@bitsolve.in