Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: select master detail record in one row

    I 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 nameaymentDetails
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @student table (
       student_id   int
     , student_name char(10)
    )
    
    INSERT INTO @student (student_id, student_name)
              SELECT 1, 'John'
    UNION ALL SELECT 2, 'Marsh'
    UNION ALL SELECT 3, 'Samuel'
    
    DECLARE @payment_details table (
       student_id int
     , payment    money
     , month      char(3)
    )
    
    INSERT INTO @payment_details (student_id, payment, month)
              SELECT 1, 1000, 'Jan'
    UNION ALL SELECT 1, 2000, 'Feb'
    UNION ALL SELECT 1, 3000, 'Mar'
    UNION ALL SELECT 2, 1000, 'Jan'
    
    SELECT s.student_id
         , s.student_name
         , Max(CASE WHEN p.month = 'Jan' THEN p.payment END) As jan
         , Max(CASE WHEN p.month = 'Feb' THEN p.payment END) As feb
         , Max(CASE WHEN p.month = 'Mar' THEN p.payment END) As mar
    FROM   @student As s
     LEFT
      JOIN @payment_details As p
        ON s.student_id = p.student_id
    GROUP
        BY s.student_id
         , s.student_name
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    Code:
    try this using pivot
    
    	 SELECT Student_id,student_name,[jan],[feb],[mar]
    	 FROM (	SELECT s.student_id,s.student_name,p.month,p.payment
            	FROM   @student As s
                LEFT  JOIN @payment_details As p ON s.student_id = p.student_id)s
    	PIVOT (MAX(payment) FOR Month IN ([jan],[feb],[mar]))s

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do this sort of manipulation in the presentation layer. As you may have noticed, the storage layer is not adept at this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •