Converting data from columns in to 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 2005 Forums

Transact-SQL (2005)

Converting data from columns in to rows

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-09-16 : 07:08:08

Hello

I have an employee course table storing employee course data:

Emp_ref | Course
0000123 | Net L1
0000123 | Dev L1
0000123 | H&S L1
0000134 | PM L2
0000134 | CM L2
0000167 | H&S L1
0000167 | CM L2
0000187 | H&S L2

As you can see employees can be on more than one course at a time.

What I need to do is convert the course data in to individual rows so I can export it in to another database.

The format needs to be as follows:

emp_ref | Course1 | Course2 | Course3
0000123 | Net L1 | Dev L1 | H&S L1
0000134 | PM L2 | CM L2
0000167 | H&S L1 | CM L2
0000187 | H&S L2

Can anyone point me in the right direction as to how this might be achieved, if indeed it can be?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 07:32:57

[code]SELECT t.Emp_Ref,
MAX(CASE WHEN t.Seq=1 THEN Course ELSE NULL END) AS Course1,
MAX(CASE WHEN t.Seq=2 THEN Course ELSE NULL END) AS Course2,
MAX(CASE WHEN t.Seq=3 THEN Course ELSE NULL END) AS Course3
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Emp_Ref ORDER BY Course) AS Seq,*
FROm YourTable)t
GROUP BY t.Emp_Ref[/code]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-16 : 09:12:49

Hi,

try with this also

Create Table #Emp (Emp_ref Varchar(100), Course Varchar(100))
Insert into #Emp (Emp_ref, Course)
Select '0000123' , 'Net L1' Union All
Select '0000123' , 'Dev L1' Union All
Select '0000123' , 'H&S L1' Union All
Select '0000134' , 'PM L2' Union All
Select '0000134' , 'CM L2' Union All
Select '0000167' , 'H&S L1' Union All
Select '0000167' , 'CM L2' Union All
Select '0000187' , 'H&S L2'

Select * , 'Course' as Lang, Row_Number() over(Partition by Emp_ref order by Emp_ref) as 'Rank' into #T
From #Emp

--Select * From #T

Declare @Str VArchar(8000), @Sql Varchar(8000)
Set @Sql = ''
Set @Str = ''

Select @Sql = @Sql + ', Min(Case when Lang = ''' + Lang + ''' and rank = '''+cast([rank] as varchar(100))+'''Then Course End)
AS "' + Lang + ' '+ cast([Rank] as varchar(100))+' "'
From (Select distinct Lang , [Rank] From #T )A

Set @Str = 'Select Emp_ref '+@sql+' From #T Group By Emp_ref'
Exec (@Str)

Drop Table #Emp, #T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 09:24:36

quote:
Originally posted by ranganath

Hi,

try with this also

Create Table #Emp (Emp_ref Varchar(100), Course Varchar(100))
Insert into #Emp (Emp_ref, Course)
Select '0000123' , 'Net L1' Union All
Select '0000123' , 'Dev L1' Union All
Select '0000123' , 'H&S L1' Union All
Select '0000134' , 'PM L2' Union All
Select '0000134' , 'CM L2' Union All
Select '0000167' , 'H&S L1' Union All
Select '0000167' , 'CM L2' Union All
Select '0000187' , 'H&S L2'

Select * , 'Course' as Lang, Row_Number() over(Partition by Emp_ref order by Emp_ref) as 'Rank' into #T
From #Emp

--Select * From #T

Declare @Str VArchar(8000), @Sql Varchar(8000)
Set @Sql = ''
Set @Str = ''

Select @Sql = @Sql + ', Min(Case when Lang = ''' + Lang + ''' and rank = '''+cast([rank] as varchar(100))+'''Then Course End)
AS "' + Lang + ' '+ cast([Rank] as varchar(100))+' "'
From (Select distinct Lang , [Rank] From #T )A

Set @Str = 'Select Emp_ref '+@sql+' From #T Group By Emp_ref'
Exec (@Str)

Drop Table #Emp, #T