January 11, 2012 at 6:18 pm
My concern is how implement the following query, thanks in advance for any help.
I am reading regarding pivot table.
I obtained 3 names (a top 3 regarding the billing)
Ex: name1, name2, name3.
I need include the 3 names with the Customer data
Id Address PostCode Message1 Message2 Message3
1 add1 5020 name1 name2 name3
2 add2 5120 name1 name2 name3
3 add3 5220 name1 name2 name3
Is possible resolve this query with pivot table
Again, thank you very much for any help.
Paul.
January 11, 2012 at 6:57 pm
Hi,
Will you be able to provide the structures of your source tables ?
--------
Manjuke
http://www.manjuke.com
January 11, 2012 at 7:41 pm
Anyway assuming you have the following table structures I have done 2 samples. One using pivot and another without using pivot.
--Sample Tables
declare @address as table(
id int
,[address] varchar(100)
,postcode int
)
declare @billing as table(
postcode int
,name varchar(50)
,billvalue money
)
--Test Data
insert into @address
select 1,'add1',5020 union
select 2,'add2',5120 union
select 3,'add3',5220
insert into @billing
select 5020,'Name A',1000 union
select 5020,'Name B',7100 union
select 5020,'Name C',1200 union
select 5020,'Name D',6300 union
select 5120,'Name E',1748 union
select 5120,'Name F',95678 union
select 5120,'Name G',12005 union
select 5120,'Name H',4300 union
select 5220,'Name I',17890 union
select 5220,'Name J',7100 union
select 5220,'Name K',5600 union
select 5220,'Name L',7300
--Pivot Sample
;with cte_pivot as (
select
addr.id
,addr.postcode
,addr.address
,bill.name
,bill.subid
from
@address as addr
cross apply (select top(3)
postcode
,name
,row_number() over(partition by postcode order by billvalue desc) as subid
from
@billing
where
addr.postcode = postcode
order by
billvalue desc
) as bill
)
select
pvt_data.id
,pvt_data.postcode
,pvt_data.address
,pvt_data.[1] as Message1
,pvt_data.[2] as Message2
,pvt_data.[3] as Message3
from
cte_pivot
pivot(max(cte_pivot.name) for cte_pivot.subid in ([1],[2],[3])) as pvt_data
--Non Pivot Sample
;with cte_pivot as (
select
addr.postcode
,bill.name
,bill.subid
from
@address as addr
cross apply (select top(3)
postcode
,name
,row_number() over(partition by postcode order by billvalue desc) as subid
from
@billing
where
addr.postcode = postcode
order by
billvalue desc
) as bill
)
select
addr.id
,addr.postcode
,addr.[address]
,max(case when pvt.subid=1 then pvt.name else 'n/a' end) as Message1
,max(case when pvt.subid=2 then pvt.name else 'n/a' end) as Message2
,max(case when pvt.subid=3 then pvt.name else 'n/a' end) as Message3
from
@address as addr
join cte_pivot as pvt on addr.postcode = pvt.postcode
group by
addr.id
,addr.postcode
,addr.[address]
Hope this will solve your issue 🙂
--------
Manjuke
http://www.manjuke.com
January 11, 2012 at 8:30 pm
Hi Manjuke, thank you very much for your time and very clear explanation.
Regards,
Paul 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply