asked on

SQL Server crosstab - can i add a percentage column for each field?

I am producing crosstabs in SQL and need to add a percentage column next to each of the field value columns. for example, in the code below, starting with column DM, the next column should be [PERCENT OF DM], where this should be the percent of the total of the DM field - this would add up to 100 for each column at the bottom of the xtab.

Is this possible? I've searched around on the web to no avail.

select MRC_YEAR,MRCRNG,[DM] ,[DM DMURL] ,[SPEC FIN],[NA] 
FROM 
(SELECT MRC_YEAR,MRCRNG,icontactid,CHANNEL,mrccode
FROM house where drops is null)    ps
PIVOT
(
Count (icontactid)
FOR CHANNEL IN
([DM] ,[DM DMURL] ,[SPEC FIN] ,[NA] )
)  AS pvt 
order by mrc_year,mrccode

Open in new window

Christopher Gordon

Is their a unique field in the cross tab results? I had a similar situation about a month ago and I ended up just writing a second cross tab query w/ the percentages and then joining the results of both cross tabs together in a query. Not sure if this will be posible in your scenario though.

ND_2007

ASKER

Well, I could make a unique field based on two fields, but I expect to this for around 50 crosstabs, so hopefully i can get each crosstab in one query. I might try this if all else fails though

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ND_2007

ASKER

That's the ticket......thanks!!

Christopher Gordon

Nice Job Sharath_123, I'll be trying that one out in the future too :)

Sharath S

thanks, happy to help you.