RE: (ot) SQL Question - flattening data

[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