Monday, July 23, 2012

How to Display row value as Column Name in SQL Server

Static Query:

SELECT branch1,branch2,Name,Id  FROM (SELECT top 5   tb.*    from  Users as tb
  ) AS PivotData
PIVOT (COUNT(Location) FOR   Branch  IN (branch1,branch2) ) AS PivotTabllumns

Dynamic Query:

  'SELECT *  FROM (SELECT top 5  tb.* from  ' +  TableName  + ' as tb
) AS PivotData
PIVOT (COUNT(' +  Columns +') FOR  ' +  Columns +' IN ( ' + CColumns + ') ) AS PivotTable'

///Above Columns and CColumns and TableName are the local variables  which are picked from temp table

Instead of "TableName" value you need to pass your table name
Instead of "Columns" On which column(ex: ID,Reference No or any column etc....) you need to perform operation
Instead of "CColumns" you have to pass your own values ex: if your checking for branch count  you need to pass branch name values i.e: branch1, branch2, branch3

If you have any doubts please let me know.

Result :

No comments:

Post a Comment