select master detail record in one row

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)

select master detail record in one row

Author

Topic

tirthadip
Starting Member

2 Posts

Posted - 2010-05-24 : 06:54:53

have two tables.

1st table has the following format:

table name:student
structure:

student_id-----student_name
1---------------- John
2---------------- Marsh
3---------------- Samuel

2nd table has the following format:

table name:paymentDetails
structure:

student_id------ payment----- month
1----------------- 1000 -------jan
1----------------- 2000 --------feb
1------------------ 3000-------- mar
2------------------- 1000-------- jan

i want an output table like following

studentname--payment-- month-- payment--- month-- payment-- month
john---------- 1000----- jan----- 2000----- feb----- 3000---- mar

please keep in mind that my details table(paymentDetails) can have any number of rows for a particular student_id

I have read a lot of articles about cross apply/cross join/cross tabs/pivots ....but cant resolve my problem

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-24 : 07:04:46

Read about Dynamic Pivot

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 09:39:58

I dont think it needs to be dynamic in this case, as he is PIVOTing on the month...can you try this.

select 
a.student_id
, a.student_name
, max(case when b.[month] = 'Jan' then b.payment else null end) as [payment1]
, max(case when b.[month] = 'Jan' then b.[month] else null end) as [month1]
, max(case when b.[month] = 'Feb' then b.payment else null end) as [payment2]
, max(case when b.[month] = 'Feb' then b.[month] else null end) as [month2]
, max(case when b.[month] = 'Mar' then b.payment else null end) as [payment3]
, max(case when b.[month] = 'Mar' then b.[month] else null end) as [month3]
.
.
.
.
.
from student a
inner join paymentdetails b on a.student_id = b.student_id
group by a.student_id
, a.student_name

EDIT: This result-set makes more sense to me than your expected output

select 
a.student_id
, a.student_name
, max(case when b.[month] = 'Jan' then b.payment else null end) as [Jan]
, max(case when b.[month] = 'Feb' then b.payment else null end) as [Feb]
, max(case when b.[month] = 'Mar' then b.payment else null end) as [Mar]
from student a
inner join paymentdetails b on a.student_id = b.student_id
group by a.student_id, a.student_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources