tsql pivot help - need help pivoting dialer stats

Question

i cant figure this out for the life of me -

   select 
*


from
(
    select 
Columnno 
,dt 
,Loans 
,[Dials Attempted]
,dpl
,aband
,contact
,success
,conv
--,case when dt is not null then 1

from 


   data
) sourcetable

this query will produce results as

enter image description here

my desired results would be

enter image description here

ive tried a few things but it just errors out, help, im lost. Thanks in advance.

To do this you will have to use dynamic sql in order to construct list of columns to be displayed. The problem is already solved - please check this post http://stackoverflow.com/questions/18317732/sql-server-pivot-table-with-joins-and-dynamic-columns — Maciej Nowicki, Apr 21 '15 at 20:49
@MaciejNowicki thanks, ill look at the link in a bit, i ended up figuring it out. posted answer below. — lookslikeanevo, Apr 21 '15 at 21:29

score 0 · Accepted Answer · answered Apr 21 '15 at 21:28

This is what i ended up doing

:

select 

labels, [1],[2]

from(
    select 
    *
    ,case when labels = 'Date' then 1 
            when labels = 'Loans' then 2
                When labels = 'Dials Attempted' then 3
                    when labels ='DPL' then 4
                        when labels = 'Abandoned' then 5
                            when labels = 'Contacts' then 6
                                when labels = 'Success' then 7
                                    when labels ='Conversion' then 8
                                        end rownum


    from
    (
        select 

        convert(varchar, Columnno ) Columnno
     ,convert(varchar, dt ) Date
        ,convert(varchar, Loans ) Loans
        ,convert(varchar, [Dials Attempted]) 'Dials Attempted'
        ,convert(varchar, dpl) DPL
        ,convert(varchar, aband) Abandoned
        ,convert(varchar, contact) Contacts
        ,convert(varchar, success) Success
        ,convert(varchar, conv) Conversion
        --,case when dt is not null then 1

        from 
        data
    ) sourcetable


    unpivot ( [Data] for  labels in (Date,Loans, [Dials Attempted], DPL, Abandoned, Contacts, Success, Conversion ) ) pivots) sourced

pivot( max(Data) for Columnno in ([1],[2])) pvt

order by rownum

This is what it produced

enter image description here

It's OK, but if you want to have more than 2 columns containing values, you have to modify query or use dynamic SQL as i've said before. Cheers! BTW. You should not answer your own question - you should modify your original question adding results of research. — Maciej Nowicki, Apr 22 '15 at 10:01

tsql pivot help - need help pivoting dialer stats

1 Answers1