Re: (ot) SQL Question - flattening data
Just read the original post properly - please ignore me. Dominic 2009/11/15 Dominic Watson > 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): > > > >User>Codes > > > > > > #user > > #code# > > > > > > > > > Dominic > > 2009/11/13 Rick Root > > >> 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
Re: (ot) SQL Question - flattening data
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): User>Codes #user #code# Dominic 2009/11/13 Rick Root > > 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:328396 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
You wrote a pivot query without using pivot. BTW, the aggregate for the pivot query can be Count(). -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Friday, November 13, 2009 4:19 PM To: cf-talk Subject: Re: (ot) SQL Question - flattening data >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:328395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL Question - flattening data
>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:328377 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
Actually, if MSSQL 2005+ is being used, PIVOT might come in handy here. I had a procedure that used cursors. It ran for 2 minutes. I converted it to use PIVOT instead and I get the same results in 2 seconds! Steve -Original Message- From: Dave Phelan [mailto:dphe...@lifepoint.com] Sent: Friday, November 13, 2009 1:11 PM To: cf-talk Subject: RE: (ot) SQL Question - flattening data 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 RickX 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:rick.r...@gmail.com] 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:328357 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
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 RickX 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:rick.r...@gmail.com] 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
