question

sham282 avatar image
sham282 asked

Arrange sql table column rows

I have a table like that ![alt text][1] I want to convert this table to following ![alt text][2] How is it possible [1]: /storage/temp/1065-aaa.jpg [2]: /storage/temp/1066-bbb.jpg
sql servertables
aaa.jpg (15.7 KiB)
bbb.jpg (15.2 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is that from Excel?
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
It looks like you want the values from the Numbers column to pull up by one row. In order to make it happen if you can join the table with itself on the off-by-one basis and update the table at the same time. The tricky part is that in order to join like this, you have to have a way to reliably order the rows. Based on the data in question you don't have such column in the table (the one you can use for reliable ordering), and thus, the script below does what you need but without any guarantee:

declare @t table (Name char(3) not null, Numbers varchar(6) not null);

insert into @t values ('Abc', ''), ('', '123'), ('Def', ''), ('', '456'), 
('', '789'), ('Ghi', ''), ('', '101112');

-- data before the update
select * from @t;

Name Numbers
---- -------
Abc  
     123
Def  
     456
     789
Ghi  
     101112

-- join the table with itself on the off-by-one and update it
-- setting the numbers column from the "next" row thus kinda
-- making the column to "pull up" by one row.
;with records as
(
    select
        Name, Numbers, row_number() over (order by (select null)) N
        from @t
)
    update a
    set Numbers = isnull(b.Numbers, '')
        from records a left join records b
            on a.N + 1 = b.N;

-- data after the update
select * from @t;
Name Numbers
---- -------
Abc  123
     
Def  456
     789
     
Ghi  101112

Hope this helps. Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.