view multiple records in a single row

sa32529 · #1 (**permalink**) 01-03-12, 05:40

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 -

Wim · #2 (**permalink**) 01-03-12, 07:30

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'

r937 · #3 (**permalink**) 01-03-12, 08:30

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

Wim · #4 (**permalink**) 01-03-12, 09:02

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.

r937 · #5 (**permalink**) 01-03-12, 09:45

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"

Pat Phelan · #6 (**permalink**) 01-03-12, 11:35

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

sa32529 · #7 (**permalink**) 01-04-12, 00:53

Quote:

Originally Posted by r937

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

Maxine2012 · #8 (**permalink**) 01-04-12, 08:44

very useful for me too. thanks a lot.

Internet Services

Web Development

Digital Marketing

Consumer Tech