Sql Server 2008 table row to table column conversion

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

HI all,

I have a table in Sql server 2008 which is dynamically populated by a Sql script , this script may insert 100 to 100000 or more rows to this table. my issue is I need one field to be displayed as table column and the other as it value.
here are the details

Table Name: Class
Fields:
Class varchar
Rollno varchar
Subject varchar
Marks varchar

Select * from class will display in this way

Class Rollno Subject Marks
1 1 eng 56
1 2 eng 70
1 3 eng 80
1 4 eng 10
1 1 maths 67
1 2 maths 89
1 3 maths 90
1 4 maths 45
1 1 Sci 45
1 2 Sci 56
1 3 Sci 76
1 4 Sci 89
2 1 eng 68
2 2 eng 65
2 1 Sci 70
2 2 Sci 65
3 1 French 89
3 1 Spanish 55
3 1 English 65
3 2 French 56
3 2 Spanish 24
3 2 English 78
3 3 French 65
3 3 Spanish 34
3 3 English 56
could have n number of records reaching 100000 plus more rows

I need to display it in this fashion:

Class Rollno Eng Maths Sci French Spanish
1 1 56 67 45
1 2 70 89 56
1 3 80 90 76
1 4 10 45 89
2 1 68 70
2 2 65 65
3 1 65 89 55
3 2 78 56 24
3 3 56 65 34

I have tried Pivot and it did not work for me , may be I did it in a wrong way

I request you to help me with this issue as its urgent

regards,

Aspboomer

Posted 7-Nov-13 5:08am

aspboomer

Add a Solution

Comments

ZurdoDev 7-Nov-13 12:31pm

It sounds like you want pivot.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thava Rajan · Answer 1 · 2013-11-07T17:59:00

SQL

DECLARE @tbl AS TABLE (class INT, rollno INT, Subject VARCHAR (100), MARK INT)

INSERT INTO @tbl
VALUES
  (1, 1, 'eng', 56), (1, 2, 'eng', 70),
 (1, 3, 'eng', 80), (1, 4, 'eng', 10),
 (1, 1, 'maths', 67), (1, 2, 'maths', 89),
 (1, 3, 'maths', 90), (1, 4, 'maths', 45),
 (1, 1, 'Sci', 45), (1, 2, 'Sci', 56),
 (1, 3, 'Sci', 76), (1, 4, 'Sci', 89),
 (2, 1, 'eng', 68), (2, 2, 'eng', 65),
 (2, 1, 'Sci', 70), (2, 2, 'Sci', 65)

SELECT Class, Rollno ,
sum(CASE WHEN SUBJECT='Eng' THEN MARK ELSE 0 END) AS Eng,
sum(CASE WHEN SUBJECT='Sci' THEN MARK ELSE 0 END) AS Science,
sum(CASE WHEN SUBJECT='Maths' THEN MARK ELSE 0 END) AS Maths
FROM   @tbl
GROUP BY class ,rollno

add the subjects in the case condition as per your requirement

Anudeep Jaiswal - MCA · Answer 2 · 2013-11-07T18:02:00

Please Check...

You Can Do It With Pivot.. But I Use This Method For Pivoting.

I Am Using #Temp Table To Execute Query
----------------------------------------------------------------------------------------------

SQL

--Select * into #Temp
--From
--(
--SELECT 1     Class,
--       1     Rollno,
--       'eng' Subject,
--       56    Marks
--UNION ALL
--SELECT 1,
--       2,
--       'eng',
--       70
--UNION ALL
--SELECT 1,
--       3,
--       'eng',
--       80
--UNION ALL
--SELECT 1,
--       4,
--       'eng',
--       10
--UNION ALL
--SELECT 1,
--       1,
--       'maths',
--       67
--UNION ALL
--SELECT 1,
--       2,
--       'maths',
--       89
--UNION ALL
--SELECT 1,
--       3,
--       'maths',
--       90
--UNION ALL
--SELECT 1,
--       4,
--       'maths',
--       45
--UNION ALL
--SELECT 1,
--       1,
--       'Sci',
--       45
--UNION ALL
--SELECT 1,
--       2,
--       'Sci',
--       56
--UNION ALL
--SELECT 1,
--       3,
--       'Sci',
--       76
--UNION ALL
--SELECT 1,
--       4,
--       'Sci',
--       89
--UNION ALL
--SELECT 2,
--       1,
--       'eng',
--       68
--UNION ALL
--SELECT 2,
--       2,
--       'eng',
--       65
--UNION ALL
--SELECT 2,
--       1,
--       'Sci',
--       70
--UNION ALL
--SELECT 2,
--       2,
--       'Sci',
--       65
--UNION ALL
--SELECT 3,
--       1,
--       'French',
--       89
--UNION ALL
--SELECT 3,
--       1,
--       'Spanish',
--       55
--UNION ALL
--SELECT 3,
--       1,
--       'eng',
--       65
--UNION ALL
--SELECT 3,
--       2,
--       'French',
--       56
--UNION ALL
--SELECT 3,
--       2,
--       'Spanish',
--       24
--UNION ALL
--SELECT 3,
--       2,
--       'eng',
--       78
--UNION ALL
--SELECT 3,
--       3,
--       'French',
--       65
--UNION ALL
--SELECT 3,
--       3,
--       'Spanish',
--       34
--UNION ALL
--SELECT 3,
--       3,
--       'eng',
--       56
--)X


 Select * from #Temp M


Select Distinct Class,RollNo,
IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Eng'),0) Eng
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Maths'),0) Maths
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Sci'),0) Sci
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'French'),0) French
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Spanish'),0) Spanish
 from #Temp M

Sql Server 2008 table row to table column conversion

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0