Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: view multiple records in a single row

    Id account num acc_type
    42 1376200071278 gl
    42 1308111111111 ic
    42 1291111111111 os
    34 1245200000000 gl
    34 1132485111111 ic


    this is table structure
    there are multiple records like this in a table . I need output as

    id gl accountnum ic accountnum osaccountnum
    42 1376200071278 1308111111111 1291111111111
    34 1245200000000 1132485111111 -

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT ids.id
    	,COALESCE(gl.account, '-') as gl_AccountNum
    	,COALESCE(ic.account, '-') as ic_AccountNum
    	,COALESCE(os.account, '-') as os_AccountNum
    FROM (SELECT DISTINCT ID
    	FROM #DaTable
    	) AS ids
    	LEFT OUTER JOIN #DaTable as gl ON
    		ids.id = gl.id AND
    		gl.Acc_Type = 'gl'	
    	LEFT OUTER JOIN #DaTable as ic ON
    		ids.id = ic.id AND
    		ic.Acc_Type = 'ic'	
    	LEFT OUTER JOIN #DaTable as os ON
    		ids.id = os.id AND
    		os.Acc_Type = 'os'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id
         , MAX(CASE WHEN type = 'gl'
                    THEN accountnum
                    ELSE NULL END) AS gl_accountnum
         , MAX(CASE WHEN type = 'ic'
                    THEN accountnum
                    ELSE NULL END) AS ic_accountnum
         , MAX(CASE WHEN type = 'os'
                    THEN accountnum
                    ELSE NULL END) AS os_accountnum
      FROM daTable
    GROUP
        BY id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Rudy,

    I compared your solution with mine (Estimated Execution Plan).
    - LEFT OUTER JOIN solution : 63% relative execution cost
    - GROUP BY solution : 37% relative execution cost

    Yours is definitely the winner.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    it's a technique that also has the benefit of working when there is (incorrectly?) more than one row of an account type, which would blow up the join solution

    i'm gonna trademark the phrase "defensive sql"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The phrase "Defensive SQL" would be a challengle to trademark. I've got 15 year old documents (written in WordPerfect 4.2 no leess) using that phrase!

    It is frightening how far I've come since writing that paper, but defensive coding is still needed as much today as it was then!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2012
    Posts
    2

    Wink

    Quote Originally Posted by r937 View Post
    Code:
    SELECT id
         , MAX(CASE WHEN type = 'gl'
                    THEN accountnum
                    ELSE NULL END) AS gl_accountnum
         , MAX(CASE WHEN type = 'ic'
                    THEN accountnum
                    ELSE NULL END) AS ic_accountnum
         , MAX(CASE WHEN type = 'os'
                    THEN accountnum
                    ELSE NULL END) AS os_accountnum
      FROM daTable
    GROUP
        BY id


    thank u very much

  8. #8
    Join Date
    Jan 2012
    Posts
    1

    hi

    very useful for me too. thanks a lot.

Tags for this Thread

Posting Permissions

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