thukralz


Hello Everyone,

I have 2 sampel tables something like this :

Table : UserInfo

Email - PK

Name

Address

Table : UserRegistration

Email - FK

Course

Sample Data for UserInfo

harsimrat@........ , Harsimrat Thukral ,Some Street

data for UserRegistration

harsimrat@....... , CS 101

harsimrat@.......,CS 201

harsimrat@........,,CS301

harsimrat@......., CS401

Now If I say something like this

SELECT * FROM UserInfo, UserRegistration

WHERE Email = 'hthukral@......'

It returns me 4 rows with repeated data, which is correct ....But I want to have somthing like this:

Columns : Name, Address, Email, Course1, Course 2, Course 3, Course 4

There can only be maximum of 4 courses, How can I get the desired results

Thanks,

Harsimrat Thukral




Re: How to do this Query ?

Kent Waldrop Au07


Harsimrat:

You ought to be able to use ROW_NUMBER() and either MAX / CASE or ROW_NUMBER() and PIVOT to get what you want. I am assuming here that you are using SQL Server 2005 and not SQL Server 2000; please verify which version you are using.

An example of a pivot version:

Code Snippet

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select EMail,
row_number() over
( partition by EMail order by Course
) as Seq,
Course
from ( select
'harsimrat@' as EMail, 'CS101' as Course union all
select
'harsimrat@', 'CS201' union all
select
'harsimrat@', 'CS301' union all
select
'Harsimrat@', 'CS401'
) baseTable

) a
pivot( max(Course) for seq in ([1],[2],[3],[4])
) pv

/*
Email course1 course2 course3 course4
---------- ------- ------- ------- -------
harsimrat@ CS101 CS201 CS301 CS401
*/

The part in red is a derived table that I used instead of creating a table, populating the table and then using the table. For your query simply substitute

Code Snippet
from userRegistration

You will also need to join to your other table as appropriate.






Re: How to do this Query ?

Harsimrat

Sorry, the above query worked, but I was wondering how to do a little imporvement.

The User Registration Table has another column which tells how the Courses are numbered, when user saves the, it is saved as which course is Course 1 and Course 2 and so on...But when I execute the above query Course 1 is differnt then what it is originally saved.

This is the actual table :

Table : UserRegistration

Email - FK

Course

Number --- New Column

data for UserRegistration

harsimrat@....... , CS 101, 1

harsimrat@.......,CS 201, 2

harsimrat@........,,CS301, 3

harsimrat@......., CS401, 4

Now how can I use the new column and form the query and still get the desired results....

Thanks for the help.

Harsimrat






Re: How to do this Query ?

Louis Davidson

Well, I would suppose that you would have some form of control table to define the structure of the report:

create table reportControl
(
course char(5),
seq int
)
insert into reportControl
select 'CS101',1
union all
select 'CS201',2
union all
select 'CS301',3
union all
select 'CS401',4
go

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select baseTable.EMail,
reportControl.Seq,
reportControl.Course
from ( select 'harsimrat@' as EMail, 'CS101' as Course
union all
select 'harsimrat@', 'CS201'
union all
select 'harsimrat@', 'CS301'
union all
select 'Harsimrat@', 'CS401') as baseTable
join reportControl
on baseTable.course = reportControl.course
) a
pivot( max(Course) for Seq in ([1],[2],[3],[4])
) pv