Transposing tables with multiple columns

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 2000 Forums

SQL Server Development (2000)

Transposing tables with multiple columns

Author

Topic

mk1matt
Starting Member

7 Posts

Posted - 2008-09-16 : 09:13:27

I'm having a real problem converting a table into a format suitable for one of my end users and was wondering if anyone could shed some light on a solution. Apologies for the long post!

Here's a small sample of the source table:


rating_id  ki_uq_id  ki_name     qual       emp_yn
---------------------------------------------------------
39065      4578      Mr Smith    Relevant   Y
4568       9874      Mrs Jones   Other      Y
4568       7894      Mr Jones    Relevant   N

The primary key for the table is the ki_uq_id. What I need to produce is one row per rating_id, showing something similar to this:


rating_id  ki_name_1  qual_1   emp_yn_1  ki_name_2  qual_2   emp_yn_2
---------------------------------------------------------------------
39065      Mr Smith   Relevant Y
4568       Mrs Jones  Other    N         Mr Jones   Relevant N

What I've done above is a much scaled down version of the full table. The table itself has 11 columns to be repeated, with a maximum of 9 people per rating_id. And here's the big number which makes cursor processing slow... there are 92,000 records, for 5,200 distinct rating_id's.

My first plan was to open a cursor on the table, sort by rating_id, then put am incrementing number against each person, resetting to 1 each time the rating_id changes. This would have the effect of numbering each person within a rating_id. I would then run 9 individual updates into the final table, one for each set of people (e.g. the first query would be for everyone with a 1, the second for everyone with a 2 etc), and match them up using the rating_id.

It's a bit of a clunky solution so I was hoping someone could come up with something a little more elegant. I don't mind doing the whole thing with a cursor but I'm concerned about performance.

Thanks,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 09:33:26

[code]SELECT r.rating_id,
MAX(CASE WHEN r.Seq=1 THEN ki_name ELSE NULL END) AS ki_name1,
MAX(CASE WHEN r.Seq=1 THEN qual ELSE NULL END) AS qual1,
MAX(CASE WHEN r.Seq=1 THEN emp_yn ELSE NULL END) AS emp_yn1,
...
MAX(CASE WHEN r.Seq=2 THEN ki_name ELSE NULL END) AS ki_name2,
MAX(CASE WHEN r.Seq=2 THEN qual ELSE NULL END) AS qual2,
MAX(CASE WHEN r.Seq=2 THEN emp_yn ELSE NULL END) AS emp_yn2,
....
MAX(CASE WHEN r.Seq=3 THEN ki_name ELSE NULL END) AS ki_name3,
MAX(CASE WHEN r.Seq=3 THEN qual ELSE NULL END) AS qual3,
MAX(CASE WHEN r.Seq=3 THEN emp_yn ELSE NULL END) AS emp_yn3,
....
...

MAX(CASE WHEN r.Seq=9 THEN ki_name ELSE NULL END) AS ki_name9,
MAX(CASE WHEN r.Seq=9 THEN qual ELSE NULL END) AS qual9,
MAX(CASE WHEN r.Seq=9 THEN emp_yn ELSE NULL END) AS emp_yn9,
...
FROM
(SELECT (SELECT COUNT(ki_uq_id) FROM YourTable WHERE rating_id=t.rating_id AND ki_uq_id > t.ki_uq_id) + 1 AS Seq,*
FROM YourTable t
)r
GROUP BY r.rating_id[/code]