Re: (ot) SQL Question - flattening data

2009-11-15 Thread 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):

table
 thead
   trthUser/ththCodes/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: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

2009-11-15 Thread Dominic Watson 

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
trthUser/ththCodes/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

RE: (ot) SQL Question - flattening data

2009-11-14 Thread Dave Phelan 

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

(ot) SQL Question - flattening data

2009-11-13 Thread Rick Root 

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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread Dave Phelan 

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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread DURETTE, STEVEN J (ATTASIAIT) 

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

2009-11-13 Thread 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:328377
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4