CODEWITHSUNDEEP
sqlsql-server
Satya Prakash
Satya Prakash

Reputation: 11

Convert SQL data in one format to another

Having data in Below format:-

valleyname value

Valley 1   0  
Valley 1   22

Valley 1   74

Valley 1   116  
Valley 1   0  
Valley 1   182  
Valley 1   184  
Valley 2    30  
Valley 3    30  
Valley 4    80  
Valley 5    60

Want to convert it into this format:-

Valley 1        Valley 2    Valley 3    Valley 4    Valley 5

22                   30        30         80        60

74                 NULL       NULL       NULL       NULL

116                NULL       NULL       NULL       NULL

182                NULL       NULL       NULL       NULL

184                NULL       NULL       NULL       NULL

how can i approach this task ?

Thanks in advance.

Upvotes: 0

Views: 63

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

You can do it like this with pivot:

DECLARE @valleys TABLE
    (
      [valleyname] VARCHAR(20) ,
      [value] INT
    );
INSERT  INTO @valleys
        ( valleyname, value )
VALUES  ( 'Valley 1', 0 ),
        ( 'Valley 1', 22 ),
        ( 'Valley 1', 74 ),
        ( 'Valley 1', 116 ),
        ( 'Valley 1', 0 ),
        ( 'Valley 1', 182 ),
        ( 'Valley 1', 184 ),
        ( 'Valley 2', 30 ),
        ( 'Valley 3', 30 ),
        ( 'Valley 4', 80 ),
        ( 'Valley 5', 60 );

WITH    vset ( valleyname, vnum, [value] )
          AS ( SELECT   valleyname ,
                        ROW_NUMBER() OVER ( PARTITION BY valleyname ORDER BY valleyname ) ,
                        [value] value
               FROM     @valleys
               WHERE    [value] <> 0
             )
    SELECT  *
    FROM    vset PIVOT ( MAX([value]) FOR vnum IN ( [1], [2], [3], [4], [5] ) ) pvt;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is tricky because you have no column to pivot on. One method uses row_number() and conditional aggregation:

select max(case when valeyname = 'Valley 1' then value end) as valley_1,
       max(case when valeyname = 'Valley 2' then value end) as valley_2,
       max(case when valeyname = 'Valley 3' then value end) as valley_3,
       max(case when valeyname = 'Valley 4' then value end) as valley_4,
       max(case when valeyname = 'Valley 5' then value end) as valley_5       
from (select t.*,
             row_number() over (partition by valleyname order by valleyname) as seqnum
      from t
     ) t
group by seqnum;

Upvotes: 1

Related Questions