Just read the original post properly - please ignore me. Dominic
2009/11/15 Dominic Watson <watson.domi...@googlemail.com> > Depending on what you are doing with this data, seems to me that this > should be done in the front end and not the db. So get your data in the > original format: > > USER, CODE > > rick,AL > rick,FR > rick,TR > rick,HS > joe,AL > joe,FU > Bob,FM > > And then use cfoutput with query and group (a very rough output here): > > <table> > <thead> > <tr><th>User></th><th>Codes</th> > </thead> > <tbody> > > <cfoutput query="theQuery" group="user"> > <tr> > <td>#user</td> > <td> > <cfoutput>#code# </cfoutput> > </td> > <tr> > </cfoutput> > > > </tbody> > </table> > > Dominic > > 2009/11/13 Rick Root <rick.r...@gmail.com> > > >> From the documentation, pivot tables seem to require aggregate >> functions... The generic description would seem to work but the >> examples make it difficult to see how. >> >> But... I figured out a solution! Using SQL Server's row_number() over >> (partition by XXX order by XXX) I can make a subquery that returns >> data like this >> >> entityid,rownum,rectyp >> >> And then run this query (tb901 is my "primary" table) >> >> select >> tb901.entityid, >> R1.rectypcd as rectype1, >> r2.rectypcd as rectype2, >> r3.rectypcd as rectype3, >> r4.rectypcd as rectype4, >> r5.rectypcd as rectype5 >> from >> tb901 >> left join >> ( >> select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >> from tb906 >> ) R1 on tb901.entityid=R1.entityid and R1.rownum=1 >> left join >> ( >> select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >> from tb906 >> ) R2 on tb901.entityid=R2.entityid and R2.rownum=2 >> left join >> ( >> select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >> from tb906 >> ) R3 on tb901.entityid=R3.entityid and R3.rownum=3 >> left join >> ( >> select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >> from tb906 >> ) R4 on tb901.entityid=R4.entityid and R4.rownum=4 >> left join >> ( >> select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >> from tb906 >> ) R5 on tb901.entityid=R5.entityid and R5.rownum=5 >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:328397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
