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
