Create custom view where rows are collapsed (see description)

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

Answer 1

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

Answer 2

User2046027549 posted

Works great.  Thank you!

Answer 3

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