SQL Server Performance

pivot data in table

Discussion in 'ALL SQL SERVER QUESTIONS' started by jamie.downs, Sep 24, 2012.

  1. jamie.downs Member

    Hi,
    If I have the following data how can I use the PIVOT statement to return it like

    team member
    --------------------
    ERP Jack
    ERP John
    ERP Mary
    CRM Bill
    CRM Mandy

    ERP CRM
    Jack Bill
    John Mandy
    Mary NULL

    I have tried the Following bu the data is returned as NULLS.

    Thanks in advance.

    drop

    table #t

    go
    create
    table #t(
    team
    varchar (20), member varchar (20)
    )

    insert
    into #t values ('ERP','Jack')

    insert
    into #t values ('ERP','John')

    insert
    into #t values ('ERP','Mary')

    insert
    into #t values ('CRM','Robert')

    insert
    into #t values ('CRM','Diana')

    select
    *from #t

    select
    team , member,row_number()over (partitionby team orderby team)as rownum from #t

    -----

    select
    [1] as CRM, [2] as ERP

    from

    (select team , member,row_number()over (partitionby team orderby team)as rownum from #t) a
    pivot(max(member)for team in([1], [2]))as pvt
  2. Shehap MVP, MCTS, MCITP SQL Server

    You can work out the below query and let me know your feedback please

    create

    table#t(

    team

    varchar (20),membervarchar (20)

    )

    insert

    into#tvalues ('ERP','Jack')

    insert

    into#tvalues ('ERP','John')

    insert

    into#tvalues ('ERP','Mary')

    insert

    into#tvalues ('CRM','Robert')

    insert

    into#tvalues ('CRM','Diana')

     

    -----

    select

    *

    from

    (

    selectTEAM,COUNT(1)ASCOUNT_MEMBERfrom#tGROUPBYTEAM)a

    pivot(AVG(COUNT_MEMBER)forteamin([CRM],[ERP]))aspvt

    DROPTABLE#t

    
  3. jamie.downs Member

    Hi Shehap,

    Thanks for the reply. It give the sum of values for the two groups

    team member
    ERP Jack
    ERP John
    ERP Mary
    CRM Robert
    CRM Diana

    CRM ERP
    2 3

    What Ineed is the actual values like

    ERP CRM
    Jack Robert
    John Diana
    Mary NULL

    Do you know if this is possible?

    thanks again.
    Jamie.
  4. Shehap MVP, MCTS, MCITP SQL Server

    By default, this is inapplicable since Pivot query should fundamentally use aggregation functions like Avg , Sum, Count , Max,, Min which it sounds like we have not exist here but exporting numerical values for pivot query

    But we still have more than one workaround using the aggregation function " MinorMax and while loops to export string values as follows

    createtable#t(

    team

    varchar (20),membervarchar (20)

    )

    insert

    into#tvalues ('ERP','Jack')

    insert

    into#tvalues ('ERP','John')

    insert

    into#tvalues ('ERP','Mary')

    insert

    into#tvalues ('CRM','Robert')

    insert

    into#tvalues ('CRM','Diana')

    Createtable#temp(CRMVarchar (100),ERPvarchar (100))

    declare@countint=(selectcount(1)from#t)

    while (@count>0)

    begin

    insertinto#temp

    select

    *

    from

    (

    selectTEAM,MEMBERfrom#t)a

    pivot(min(MEMBER)forteamin([CRM],[ERP]))aspvt

    delete#tfrom#tinnerjoin#tempon ((#t.member=#TEMP.ERP)or(#t.member=#TEMP.crm))

    select@count=count(1)from#t

    end

    select*from#temp

    DROPTABLE#t

    droptable#temp

    Kindly work out it and let me know your feedback
  5. john1234 New Member

Share This Page