Is there a particular reason to return them in this format? I would think that the straight query output would be simpler to work with. However, you can accomplish this either by using cursors to loop over the query output and build what you are looking for or by building a crosstab query of the data. I haven't built a crosstab query in quite a while and don't remember all the specifics, but the output would be similar to:
Entity AL FR TR HS FU FM Rick X X X X Joe X X Bob X Crosstab queries can be a little hairy to build. IMHO, go with the cursors. -----Original Message----- From: Rick Root [mailto:[email protected]] Sent: Friday, November 13, 2009 10:41 AM To: cf-talk Subject: (ot) SQL Question - flattening data I'm trying to flatten out some data using only SQL.... we currently have a mainframe job that produces a datafeed for me.... uses cobol to do the work of looping through all the entities and putting up to 5 record types in 5 "record type" fields in the output file. I'm trying to figure out a way to do it with SQL alone so I can just use a transact-sql job to produced my flattened reporting table. So for example, let's say I've got a table like this: create table entityRecordTypes ( entityid char(10), recordType char(2), primary key (entityid, recordType) ); How do I get from here ... rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM to a view or table that has this structure .... entityid,rectype1,rectype2,rectype3,rectype4,rectype5 rick,AL,FR,TR,HS,NULL joe,AL,FU,NULL,NULL,NULL bob,FM,NULL,NULL,NULL,NULL using SQL. if an entity had more than 5 record types, only the first 5 would be put into the output table/view. Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328354 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
