How to rotate a table horizontally in sql server

Question

i have table with columns as :

Sr.no  Subject  No of class attended    
-------------------------------------
1       English           3
2       Maths             4
3       SocialScience     5

I want the table in this format

English    Maths   SocialScience
---------------------------------
3            4          5

I tried this:

Select case when subject ='Maths' then COUNT(No_of_Candidates) else null  end as Maths

but with this i get the data like this :

 English    Maths   SocialScience
---------------------------------
   3            
               4
                         5

Please help me how should i resolve this..

Solution 1

As you said you don't want the output like this:

 English    Maths   SocialScience
---------------------------------
   3            
               4
                         5

You need to use Subquery like this:

SELECT English,Maths,SocialScience
FROM (
SELECT Subject,No_of_class_attended
  FROM mytable) up
PIVOT 
(Sum([No_of_class_attended]) 
 for Subject in ([English],[Maths],[SocialScience])) p

Output:

English    Maths   SocialScience
---------------------------------
3            4          5

  

See this SQLFiddle

For more see SQL SERVER – PIVOT and UNPIVOT Table Examples

Solution 2

Using PIVOT

SELECT *
FROM yourtable
PIVOT 
(Sum([No of class attended]) for Subject in ([English],[Maths],[SocialScience])) p

Solution 3

podiluska solution is correct, I just would like to share a dynamic solution if you would like to add other subjects into the table, and do not want to change the query. However it has some limitation around the length, but you can use it for certain situations for sure:

DECLARE @SQL nvarchar(MAX)
DECLARE @ColNames nvarchar(max)

SET @ColNames = ''

SELECT @ColNames = (CASE WHEN subjects.Subject IS NOT NULL THEN @ColNames + '[' + subjects.Subject + '],' ELSE '' END)
FROM subjects

SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)


EXEC('SELECT *
FROM subjects
PIVOT 
(Sum([classNum]) for Subject in (' + @ColNames + ')) p')

Here is an SQL Fiddle.

Solution 4

If you don't want to use the PIVOT keyword, you can simply use MAX:

Select MAX(case when subject ='Maths' then No_of_Candidates else null  end) as Maths
,MAX(case when subject ='English' then No_of_Candidates else null  end) as English
,MAX(case when subject ='SocialScience' then No_of_Candidates else null  end) as SocialScience
from tableName 

If the table contains results for multiple students for example, you would need to use GROUP BY, for example:

Select MAX(case when subject ='Maths' then No_of_Candidates else null  end) as Maths
,MAX(case when subject ='English' then No_of_Candidates else null  end) as English
,MAX(case when subject ='SocialScience' then No_of_Candidates else null  end) as SocialScience
from tableName Group By StudentID

Any value is greater than NULL, therefore the MAX will eliminate NULLs

View more solutions

Comments

[Swift - Friday Pie]

After having same question but finding this one, I cannot resist joke, for my original wording was "how to flip a table" before I realized the pun.

[Himanshu]

OP already have this type of output. He/She do not need this output.

[user1274646]

Cannot perform an aggregate function on an expression containing an aggregate or a subquery. i get this error if i use max function..how shal i resolve this?

[Sam Anwar]

Oh, I see, just remove the COUNT function altogether from all 3 expressions and let me know what you get. I revised the code above.

[user1274646]

if i remove Count i will not be able to count the no_of_candidates coz it actuly counts the no of rows from the table..

[Sam Anwar]

Ok, I guess your first question is a bit confusing then. So do you need to do anything with the numbers shown in the table (no of class attended)? In the example you included, you are not counting anything, you're just transforming the existing numbers in the 3rd column into rows. But then you listed the count functions as COUNT(No_of_Candidates)! Where did the No_of_Candidates column come from?

[Himanshu]

Yeh, but given code by you in this answer will not satisfy OP.

[Himanshu]

But we should write clear (or exact) answer (code) which OP require. See the result of your code in this fiddle. OP have told in the question that he do not require that result.

[Andriy M]

I think you are being a bit too harsh on @hims056, and without recognising a flaw in your answer too. Would you like me to point out the mistake or do you prefer to do it yourself?

[Andriy M]

@podiluska's answer isn't quite correct, and neither is yours. Have you actually taken a look at your query's output? It produces results in multiple rows whereas the OP wants them in a single row.