SQL Change Format result

Question

Hello,

I have an query and the result is like this

But i want the result using below format

Can we change the format using SQL command?

Answer 1

Hi @Handian Sudianto

I took a portion of your data and simplified the numbers to save time, and ran a test.

create table test(Timestamp datetime,
AVERAGE_of_Recv_Percemt_Utilization int,
AVERAGE_of_Xmit_Percemt_Utilization int);
insert into test values
('5/3/2023 1:47:54 AM',82,6),
('5/3/2023 1:48:54 AM',38,7),
('5/3/2023 1:49:54 AM',40,14);


;with T1 as(
  select 'AVERAGE_of_Recv_Percemt_Utilization' as a,
   case when Timestamp = '5/3/2023 1:47:54 AM'
       then AVERAGE_of_Recv_Percemt_Utilization end as b,
   case when Timestamp = '5/3/2023 1:48:54 AM'
	   then AVERAGE_of_Recv_Percemt_Utilization end as c,
   case when Timestamp = '5/3/2023 1:49:54 AM'
	   then AVERAGE_of_Recv_Percemt_Utilization end as d
  from test
),T2 as(
  select a,max(b) as [5/3/2023 1:47:54 AM],
		   max(c) as [5/3/2023 1:48:54 AM],
		   max(d) as [5/3/2023 1:49:54 AM] from T1 group by a
),T3 as(
  select 'AVERAGE_of_Xmit_Percemt_Utilization' as e,
   case when Timestamp = '5/3/2023 1:47:54 AM'
       then AVERAGE_of_Xmit_Percemt_Utilization end as f,
   case when Timestamp = '5/3/2023 1:48:54 AM'
	   then AVERAGE_of_Xmit_Percemt_Utilization end as g,
   case when Timestamp = '5/3/2023 1:49:54 AM'
	   then AVERAGE_of_Xmit_Percemt_Utilization end as h
  from test
),T4 as(
   select e,max(f) as [5/3/2023 1:47:54 AM],
		   max(g) as [5/3/2023 1:48:54 AM],
		   max(h) as [5/3/2023 1:49:54 AM] from T3 group by e)
select * from T2
union all
select * from T4;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

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.

Answer 2

To do what you want in SQL Server, you would need to engage in Dynamic SQL. I have a long article on my web site that discusses the good and bad with dynamic SQL. This link takes you directly to the section on dynamic pivot: https://www.sommarskog.se/dynamic_sql.html#pivot.

But if you have little experience of dynamic SQL, you may find the material quite daunting. Dynamic SQL certainly is no beginner-level feature.

In many cases, you are better of pivoting in the presentation layer. After all, it is a non-relational operation that is performed exactly for presentation purposes.