question

b.joseph avatar image
b.joseph asked

How to convert Rows into Columns in SQL Server

Hi,

I have a table like below, I want to transpose it like excel. Please help me how can a do this. I am not very good at Querying

CANDIDATEID  SUB                     SUBID 	   MARK	   TOTAL_QUESTIONS
      41	 IT	                    1	    6	   14
      41	 MATH	                    2	    3	   10
      41	 ENGLISH READING            3	    4	   10
      41	 ENGLISH GRAMMER            4	    3	   15
      42	 IT	                    1	    3	   14
      42	 MATH	                    2	    2	   10
CANDIDATEID IT_MARKS MATH_MARKS ER_MARKS EG_MARKS T_QNS_IT T_QNS_MATH T_QNS_ER T_QNS_EG
41            6           3          4         3   14       10         10       15
42            3           2         NULL      NULL 14       10         10       15
<br>
sql server 2012pivot
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@b.joseph It looks like the data has some errors in it. The first row with CANDIDATEID = 41 and SUBID = 1 has the SUB value "IT". At the same time the fifth row with the same SUBID = 1 has the SUB value "INFORMATION TECHNOLOGY". How is it even possible? If SUBID identifies the SUB then there should never be 2 different values for the same SUBID. Of course it is possible to just use the SUBID for pivoting and make up the column headers arbitrarily, so please clarify whether this is the case. Also, it is important to know whether the number of rows per CANDIDATEID is limited to 4, or it is possible that some other candidate has more rows with SUBID not matching any of the 1 through 4 provided in the sample data. Please clarify. Thank you.

0 Likes 0 ·

Hi Oleg,I am very sorry both are IT i just shotented in the first row and forgot to change the next .Thank you

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.