none
PIVOT on more two columns

    Question

  • accountid ItemName Status UnitsRemaining

    1 Login as Rep to Train Complete 0 1 Show "Support" link Started 1

    Output Looking for is in below picture




    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.




    • Edited by Cool Mind Friday, November 15, 2013 12:54 AM
    Friday, November 15, 2013 12:40 AM

Answers

  • You can try this way.First unpivot status and Unitremaining Columns then pivot the data.

    Declare @Source table (accountid int ,ItemName varchar(40),Status varchar(10),UnitsRemaining int);
    Insert into @Source values(1,'Login as Rep to Train','Complete',0)
    Insert into @Source values(1,'Show "Support" link','Started',1)
    
    
    ;With Unpvt
    as
    (
    select accountid,ItemName+' '+ColumnName as unpvtColumn,Value
    from 
    	(	select accountid,ItemName,Status,Convert(varchar(10),UnitsRemaining) as Units 
    		from @Source
    	)
     as u
    unpivot
    	(	Value for ColumnName in (Status,Units)
    		) as unpvt
    )
    
    select * from
    Unpvt
    pivot
    (
    Max(Value)
    For unpvtColumn in([Login as Rep to Train status],[Login as Rep to Train Units],[Show "Support" link Status],[Show "Support" link Units])
    ) as pvt
    
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Friday, November 15, 2013 1:07 AM

All replies

  • You can try this way.First unpivot status and Unitremaining Columns then pivot the data.

    Declare @Source table (accountid int ,ItemName varchar(40),Status varchar(10),UnitsRemaining int);
    Insert into @Source values(1,'Login as Rep to Train','Complete',0)
    Insert into @Source values(1,'Show "Support" link','Started',1)
    
    
    ;With Unpvt
    as
    (
    select accountid,ItemName+' '+ColumnName as unpvtColumn,Value
    from 
    	(	select accountid,ItemName,Status,Convert(varchar(10),UnitsRemaining) as Units 
    		from @Source
    	)
     as u
    unpivot
    	(	Value for ColumnName in (Status,Units)
    		) as unpvt
    )
    
    select * from
    Unpvt
    pivot
    (
    Max(Value)
    For unpvtColumn in([Login as Rep to Train status],[Login as Rep to Train Units],[Show "Support" link Status],[Show "Support" link Units])
    ) as pvt
    
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Friday, November 15, 2013 1:07 AM
  • Are these Item Names predefined? If yes, then

    ;with cte as (select *, ROW_NUMBER() over (partition by AccountID order by ItemName) as Rn
    
    from dbo.AccountsInfo)
    
    select AccountID,
    
    max(case when Rn = 1 then [Status] end) as [Login As Rep To Train Status],
    
    max(case when Rn = 1 then UnitsRemaining else 0 end) as [Login As Rep To Train Units],
    
    max(case when Rn = 2 then [Status] end) as [Show "Support" Link Status],
    
    max(case when Rn = 2 then UnitsRemaining else 0 end) as [Show "Support" Link Units]
    from cte GROUP BY AccountID
    ORDER BY AccountID



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, November 15, 2013 3:21 AM
  • You can try this way.First unpivot status and Unitremaining Columns then pivot the data.

    Declare @Source table (accountid int ,ItemName varchar(40),Status varchar(10),UnitsRemaining int);
    Insert into @Source values(1,'Login as Rep to Train','Complete',0)
    Insert into @Source values(1,'Show "Support" link','Started',1)
    
    
    ;With Unpvt
    as
    (
    select accountid,ItemName+' '+ColumnName as unpvtColumn,Value
    from 
    	(	select accountid,ItemName,Status,Convert(varchar(10),UnitsRemaining) as Units 
    		from @Source
    	)
     as u
    unpivot
    	(	Value for ColumnName in (Status,Units)
    		) as unpvt
    )
    
    select * from
    Unpvt
    pivot
    (
    Max(Value)
    For unpvtColumn in([Login as Rep to Train status],[Login as Rep to Train Units],[Show "Support" link Status],[Show "Support" link Units])
    ) as pvt
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    With some changes here & there, I was able to use this query successfully. Thank You so much for your quick reply. I really appreciate that.

    Thanks


    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.

    Friday, November 15, 2013 8:30 PM
  • So, did you check my solution? I think it's much simpler than above.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, November 15, 2013 8:43 PM