locked
Create custom view where rows are collapsed (see description) RRS feed

  • Question

  • User2046027549 posted

    First off... Thanks in advance for any help!

    I have date like...

    User Application
    zwe33zc Microsoft Excel
    zwe33zc Microsoft Outlook
    zwe33zc Microsoft PowerPoint

    I need to transpose the data to this...

    User Excel Outlook PowerPoint
    zwe33zc Microsoft Excel Microsoft Outlook Microsoft PowerPoint
    Tuesday, April 30, 2019 8:48 PM

Answers

  • User61956409 posted

    Hi struct,

    Welcome to ASP.NET forums.

    To achieve the requirement, you can try to use [the PIVOT operator](), and the following SQL statement is for your reference.

    select [User], [Microsoft Excel],[Microsoft Outlook],[Microsoft PowerPoint]
    from
    (
      select [User], [Application]
      from TestTable
    ) p
    pivot
    (
      COUNT([Application])
      for [Application] in ([Microsoft Excel],[Microsoft Outlook],[Microsoft PowerPoint])
    ) piv;

    Test Result:

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 1, 2019 3:07 AM

All replies

  • User61956409 posted

    Hi struct,

    Welcome to ASP.NET forums.

    To achieve the requirement, you can try to use [the PIVOT operator](), and the following SQL statement is for your reference.

    select [User], [Microsoft Excel],[Microsoft Outlook],[Microsoft PowerPoint]
    from
    (
      select [User], [Application]
      from TestTable
    ) p
    pivot
    (
      COUNT([Application])
      for [Application] in ([Microsoft Excel],[Microsoft Outlook],[Microsoft PowerPoint])
    ) piv;

    Test Result:

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 1, 2019 3:07 AM
  • User2046027549 posted

    Works great.  Thank you!

    Wednesday, May 1, 2019 2:00 PM
  • User77042963 posted

    The old way to PIVOT data using CASE with aggregate has better usages. Here is a sample for your requirement:

    Create table test ([User] varchar(30),[Application]  varchar(50))
    
    Insert into test values('zwe33zc','Microsoft Excel'),
    ('zwe33zc','Microsoft Outlook'),
    ('zwe33zc','Microsoft PowerPoint')
    
    
    select [User]
    , Max(Case when [Application]= 'Microsoft Excel' then 'Microsoft Excel' end) as [Excel]
    , Max(Case when [Application]= 'Microsoft Outlook' then 'Microsoft Outlook' end) as [Outlook]
    , Max(Case when [Application]= 'Microsoft PowerPoint' then 'Microsoft PowerPoint' end) as [PowerPoint]
    
    from test
    Group by [User]
    
    drop table test
    
     

    Wednesday, May 1, 2019 2:42 PM