How to display the sql data horizontal using Pivot

Question

I have attached the screenshot of the sql query and the results table, Here i would to view the table Horizontal.

like

|iFruit|iPlant|Pat on the back|The Incredebles|

|2 | 1 | 1 | 1 |

Please help me to write the pivot function to get the above format.

Answer 1

You can write your query in following order.

select <column list> from (select query) as S
Pivot
(
  sum(col)
  for col in (column list)
)as pvt

Example:

Select iFruit, iPlant, [Pat on the Back], [The Incredibles] 
 from (select r.award_name as Awardname, 
   count(r.award_name) as Awardcount
   from associate_L_award p, asociate_information q, award_list r
   where p.Id = q.id
   and p.award_no = r.award_no
   and q.id='290007'
   group by r.award_name) as S
 Pivot
 (
   sum (AwardCount)
   for AwardName in (iFruit, iPlant, [Pat on the Back], [The Incredibles]) 
 ) as pvt

Note: If your base query is returning more Award Names then you have to manually add them in this query. Plus, if you have space in award name like "Pat on the back" then enclose that string in "[]".

Edit: After a lot of RnD, I got the solution.
Scenario: Number of rows (which in turns become columns in pivot) return by the main are not fixed, they are dynamic. Solution:

1. Create a temp table to store the output of your main query. However this is not mandatory and you can use your main query where-ever required. But from performance and readability point, you may use temp table.

SELECT * 
INTO   #temp 
FROM   (SELECT r.award_name        AS Awardname, 
               Count(r.award_name) AS Awardcount 
        FROM   associate_l_award p, 
               asociate_information q, 
               award_list r 
        WHERE  p.id = q.id 
               AND p.award_no = r.award_no 
               AND q.id = '290007' 
        GROUP  BY r.award_name) AS S

2. Write following set of queries to build your pivot query.

DECLARE @str NVARCHAR(1000) 
DECLARE @sql NVARCHAR(1000)      
SELECT @str = COALESCE(@str+',', '') + awardname 
FROM   #temp 

SET @sql = N'select ' + @str + ' from (SELECT Awardname, Awardcount FROM #temp) as S 
Pivot 
(   
  sum(Awardcount)   
  for Awardname in (' + @str + ')  
)as pvt;'

3. Now you only need to execute following command to get desired result.

EXECUTE Sp_executesql @sql