Data manipulation.

Question

I have a table with following data.

Is it possible to convert the results in the following format without the use of any union. If the value in col A, B or C is zero then for same User zero else 1 in the next row of the columns?

Thanks.
SH

Answer 1

Declare @Sample Table(Metric varchar(10), [User] varchar(20), A int, B int, C int);
Insert @Sample(Metric, [User], A, B, C) Values
('C', 'BELLIS', 14, 14, 0),
('C', 'RDEKARSK', 26, 34, 7),
('C', 'AGRACE', 3, 10, 0);

Select Case When Number = 1 Then Metric Else 'D' End, 
  [USER],
  Case When Number = 1 Then A When A = 0 Then 0 Else 1 End As A,
  Case When Number = 1 Then B When B = 0 Then 0 Else 1 End As B,
  Case When Number = 1 Then C When C = 0 Then 0 Else 1 End As C
From @Sample s
Cross Join (VALUES (1), (2)) As n(Number)
Order By [User], Number;

Tom

Answer 2

Hi @Sh AS ,

Please also try with below:

 Declare @Sample Table(Metric varchar(10), [User] varchar(20), A int, B int, C int);  
 Insert @Sample(Metric, [User], A, B, C) Values  
 ('C', 'BELLIS', 14, 14, 0),  
 ('C', 'RDEKARSK', 26, 34, 7),  
 ('C', 'AGRACE', 3, 10, 0);  
   
  
select x.Metric,s.[User]  
,IIF(x.Metric='D' and A<>0,1,A) A  
,IIF(x.Metric='D' and B<>0,1,B) B  
,IIF(x.Metric='D' and C<>0,1,C) C  
from @Sample s  
cross apply (  
select 'C' UNION ALL SELECT 'D') x(Metric)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table