1

Can anyone Please help me to transpose below result

+------+------------+-------+
| SVR  |   dates    | count |
+------+------------+-------+
| SRV1 | 03/25/2018 |     1 |
| SRV1 | 04/06/2018 |    59 |
| SRV1 | 04/07/2018 |     9 |
| SRV1 | 04/13/2018 |     1 |
| SRV1 | 04/15/2018 |     1 |
| SRV1 | 04/20/2018 |     4 |
| SRV1 | 04/21/2018 |     2 |
| SRV1 | 04/27/2018 |     5 |
| SRV1 | 04/28/2018 |     5 |
| SRV1 | 04/29/2018 |     1 |
| SRV1 | 04/30/2018 |    62 |
+------+------------+-------+

Date can be variable and the range of dates are 1 month ( Getdate and getdate -30)

Server 04/01/2018 04/02/2018 04/03/2018 ....... 04/30/2018
SRV1     10         0       2       5
2
  • DT, sorry for rejecting your edit, I thought this was posted as an answer not a question! May 1, 2018 at 21:22
  • Have you checked out PIVOT? Have you made any attempts so far?
    – Vérace
    May 2, 2018 at 5:23

1 Answer 1

1

I have inserted your values into a table variable just to so I could pivot from it later.

The code came out like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

declare @t table(srv nvarchar(128),dates date,[count] int)

insert into @t (srv,dates,[count])
select 'SRV1',cast ('03/25/2018' as date) ,1   union all
select 'SRV1',cast ('04/06/2018' as date) ,59  union all
select 'SRV1',cast ('04/07/2018' as date) , 9  union all
select 'SRV1',cast ('04/13/2018' as date) , 1  union all
select 'SRV1',cast ('04/15/2018' as date) , 1  union all
select 'SRV1',cast ('04/20/2018' as date) , 4  union all
select 'SRV1',cast ('04/21/2018' as date) , 2  union all
select 'SRV1',cast ('04/27/2018' as date) , 5  union all
select 'SRV1',cast ('04/28/2018' as date) , 5  union all
select 'SRV1',cast ('04/29/2018' as date) , 1  union all
select 'SRV1',cast ('04/30/2018' as date) ,62  



select *
from (

       select srv  
              ,dates
              ,[count]
          from @t

) radhe
pivot (sum([count]) for dates in (
  [2018-04-01], 
  [2018-04-02], 
  [2018-04-03], 
  [2018-04-04], 
  [2018-04-05], 
  [2018-04-06], 
  [2018-04-07], 
  [2018-04-08], 
  [2018-04-09], 
  [2018-04-10], 
  [2018-04-11], 
  [2018-04-12], 
  [2018-04-13], 
  [2018-04-14], 
  [2018-04-15],
  [2018-04-16], 
  [2018-04-17], 
  [2018-04-18], 
  [2018-04-19], 
  [2018-04-20], 
  [2018-04-21], 
  [2018-04-22], 
  [2018-04-23], 
  [2018-04-24], 
  [2018-04-25], 
  [2018-04-26], 
  [2018-04-27], 
  [2018-04-28], 
  [2018-04-29], 
  [2018-04-30])) as krishna
where 1=1

there are things that could be changed, but that is all up to you, plus if you want to get rid of the nulls and replace them by zeros please have a look at the following question and the answers at:

How to replace (null) values with 0 output in PIVOT

And that will also give you some ideas as how to produce all this in a dynamic way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.