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:328346
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
