Help Transposing a Column into Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help Transposing a Column into Rows

Author

Topic

mickster
Starting Member

10 Posts

Posted - 2010-11-09 : 19:53:13

Hi,
I'm hoping someone can help me with this. I have the following table (it's a snapshot, not the entire table which is 91,137 rows long). Apologies for the way the table appears below.

Family Name| Subject Name
----------- --------------
ColeBrook | Ancient History
ColeBrook | Biology
ColeBrook | English Advanced
Cooper | LRC C
Cooper | Italian Continuers
Cooper | Italian Extension
Cooper | Advanced Mathematics
Cooper | Mathematics Extension 2

I need to transpose the Subject Name column to rows, so each subject name would appear as Subject 1, Subject 2, Subject 3, etc.

I'm trying to do this by using an additional 'Counter' (called SubjectNumber) column, I'm populating this column of the database by using the below statement.

DECLARE @Counter INT
SET @Counter = 0
UPDATE M
SET @Counter = SubjectNumber = @Counter + 1
FROM Misc1 M
INNER JOIN (SELECT StudentCode FROM Misc1 GROUP BY StudentCode) T ON M.StudentCode = T.StudentCode

This statement obviously doesn't take into account the different groups of family names and instead generates a sequence of numbers from 1 to 91,137.

Basically I want to have a sequence starting from 1 and ending at x (with x being the last row for that particular family name). Using the table above as an example, the Subject Number column would need to start at 1 for Colebrook and end at 3. Similarly, it would need to start at 1 for Cooper and end at 5. Note that the table is only a handful of rows. The actual table in my database is 91,137 rows long.

Once I do this I will be able to transpose the column to rows fairly easily.

Any thoughts?

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 00:04:06

Can you please post the expected o/p ?

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 01:29:17

Maybe this might help you.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152505

PBUH

mickster
Starting Member

10 Posts

Posted - 2010-11-10 : 18:28:56

Ok, I fixed the problem with the sequence using the following script

DECLARE
@iSeq int,
@iLastVal nvarchar(255)

SET @iSeq = 0

UPDATE M
SET
M.SubjectNumber = @iSeq,
@iSeq = CASE
WHEN @iLastVal = M.FamilyName THEN @iSeq + 1
ELSE 1 END,
@iLastVal = M.FamilyName
FROM Misc M

This populates the sequence correctly.

However I now have another problem with trying to transpose the rows into columns. Below are the first few rows of my table.

FamilyName| SubjectNumber| SubjectName
---------- ------------- -----------
ColeBrook | 1| Ancient History
ColeBrook | 2| Biology
ColeBrook | 3| English Advanced
Cooper | 1| LRC C
Cooper | 2| Italian Continuers
Cooper | 3| Italian Extension
Cooper | 4| Advanced Mathematics

I'd like to have my table appear as below

FamilyName| Subject1| Subject2| Subject3| Subject4
---------- -------- --------- --------- --------
ColeBrook| Ancient History| Biology|English Advanced
Cooper|LRC C|Italian Continuers|Italian Extension|Advanced Mathematics

I've tried using the following script to accomplish this but the results are not what I want as there are still multiple rows for each student and the Subject Names are not all on the same row.

SELECT
FamilyName,
CASE SubjectNumber WHEN 1 THEN SubjectName ELSE '' END as Subject1,
CASE SubjectNumber WHEN 2 THEN SubjectName ELSE '' END as Subject2,
CASE SubjectNumber WHEN 3 THEN SubjectName ELSE '' END as Subject3,
CASE SubjectNumber WHEN 4 THEN SubjectName ELSE '' END as Subject4
FROM Misc M
GROUP BY FamilyName, SubjectNumber, SubjectName

Appreciate any help, thanks

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 23:31:27

[code]
SELECT
FamilyName,
CASE SubjectNumber WHEN 1 THEN SubjectName ELSE '' END as Subject1,
MAX(CASE SubjectNumber WHEN 2 THEN SubjectName ELSE '' END) as Subject2,
MAX(CASE SubjectNumber WHEN 3 THEN SubjectName ELSE '' END) as Subject3,
MAX(CASE SubjectNumber WHEN 4 THEN SubjectName ELSE '' END) as Subject4
FROM Misc M
GROUP BY FamilyName
[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources