Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Pivot?

I have a table with many columns of which I need to pivot on two
REP and disqReason

The problem is that the disqReason currently has 4 possible different values and may climb to as many as 14 at some point

Since I don't want to come back and rebuild hard coded columns...
Any way I can do a pivot on this and WHATEVER is in disqReason gets pivoted out by rep and a count on disqReason?

so...

REP     disqReason
aBitty    Qualified
aBitty    Other
aBitty    Other
aBitty    ...many others...



Would become

Rep     Qualified     Other    ....many others...
aBitty      2                 1         other counts
etc...

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Larry Brister

ASKER

ralmada...
Perfect.  Thanks.
Working final code is attached.

Is there a way to force a particular column (only one) to be first?  And then the others?
Thanks
declare @strSQL varchar(max)
declare @cols varchar(max)

set @cols = stuff((select distinct  '], [' + disqualifyReason
			from dbo.inboundCalls order by 1 for xml path('')), 1, 2, '') + ']'

set @strSQL = 'select Rep, ' + @cols + ' from (
		select Rep, disqualifyReason, 1 as st from dbo.inboundCalls) o
		pivot (count(st) for disqualifyReason in (' + @cols + ')) as p'
exec(@strSQL)

Open in new window

Perfect...thanks
Avatar of lludden
A dynamic pivot table

DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + disqReason+ ']',
    '[' + disqReason + ']'
  )
FROM (SELECT DISTINCT disqReason FROM MyTable) T1


DECLARE @PivotQuery varchar(max)
SET @PivotQuery ='
SELECT * FROM (
SELECT Rep, disqReason , COUNT(*) AS Cnt
FROM MyTable
GROUP BY Rep, disqReason
) T1
PIVOT (
        SUM(Cnt) FOR disqReason IN (' + @PivotColumnHeaders + ')
) AS P'

EXECUTE( @PivotQuery)
>>Is there a way to force a particular column (only one) to be first?  And then the others?<<

that is the job of the "order by" in here:

set @cols = stuff((select distinct  '], [' + disqualifyReason
                  from dbo.inboundCalls order by 1 for xml path('')), 1, 2, '') + ']'


This will bring the columns alphabetically.