SQLTeam.com | Weblogs | Forums

SQL Combined Multiple rows into multiple columns


#1

I need a little help. right now I am using a group by query.

Select Distinct
[Tax ID], pro_xtyp,[Fee Schedule], [Fee Type]
from MonthEnds.dbo.vw_All_Products
Where AHS = 'X' and ProductCode ='WCP_IMW' and ST = 'PA' and
[WC and MVA Check] = 'GOOD'
Group by [Tax ID], pro_xtyp,[Fee Schedule], [Fee Type]
order by [Tax ID]

We have a few TaxID that have multiple pro_xtyp or Fees. I woud like to have this rolled up to the TAXID and have a single row. Is this possible? How can I manage to do this?

Here is some of the data that I am looking at.

image


#2

maybe this?

create table #taxmancometh(taxid int, pro_xtype varchar(50), 
fee_schedule varchar(50), feetype varchar(50))

;with cte
as
(
select 231365978 as taxid, 'PRIM' as pro_xtype, '10' as fee_schedule, 
'% of state rates' as feetype  union
select 231365978 as taxid, 'PROV' as pro_xtype, '10' as fee_schedule, 
'% of state rates' as feetype  union
select 231365978 as taxid, 'SPEC' as pro_xtype, '10' as fee_schedule, 
'% of state rates' as feetype  union
select 231365978 as taxid, 'SPEC' as pro_xtype, '15' as fee_schedule, 
'% of state rates' as feetype  union
select 231365978 as taxid, 'SPEC' as pro_xtype, '25' as fee_schedule, 
'% of state rates' as feetype  union
select 231365978 as taxid, 'SPEC' as pro_xtype, 'X' as fee_schedule , 
'Rate Sheet' as feetype 
)
insert into #taxmancometh

select * From cte

select  *
  From (
		select pro_xtype, taxid, cast(fee_schedule as int) as fee_schedule, 
feetype from #taxmancometh where fee_schedule <> 'X'
  ) x
	pivot
	(
		sum(fee_schedule) for pro_xtype in (PRIM, PROV, SPEC)
	) pvt 


drop table #taxmancometh

#3

This would be awesome! However, we have a ton of taxids. I was giving you an example of just one.


#4

I was giving you a sample query based on your sample data. that is where the hand holding ends and you come in :rofl:

;with cte
as
(

select taxid, pro_xtype,  fee_schedule ,  feetype 
from your_table
)

select  *
  From (
		select pro_xtype, taxid, cast(fee_schedule as int) as fee_schedule, 
feetype from cte where fee_schedule <> 'X'
  ) x
	pivot
	(
		sum(fee_schedule) for pro_xtype in (PRIM, PROV, SPEC)
	) pvt

#5

haha I figure that. Thanks