CODEWITHSUNDEEP
sql-servert-sql
Mikkybukky
Mikkybukky

Reputation: 3

Script that shuffle data in table variable

I was tasked to solve this but I have been battling with it for days now. can anyone help to solve it ?

1, write the T-SQL to insert the following values into a table variable, which has an identity column, 5 address lines and a postcode column:

-- address line 1: 'Churchill house'
-- address line 2: ' '
-- address line 3: '30 Boston Road'
-- address line 4: ' '
-- address line 5: 'lusaka'
-- postcode: 'e12 3re'

2, write a script to shuffle up the address lines in the table variable from Question 1

-- address line 1: 'Churchill house'
-- address line 2: ' '
-- address line 3: '30 Boston Road'
-- address line 4: ' '
-- address line 5: 'lusaka'
-- postcode: 'e12 3re'

Upvotes: 0

Views: 66

Answers (1)

SqlZim
SqlZim

Reputation: 38073

The first part is pretty straight forward:

declare @t table (
    id int identity(1,1)
  , address_line_1 varchar(64)
  , address_line_2 varchar(64)
  , address_line_3 varchar(64)
  , address_line_4 varchar(64)
  , address_line_5 varchar(64)
  , postal_code varchar(64)
);

insert into @t values ('Churchill house','','30 Boston Road','','lusaka','e12 3re')

select * from @t

returns:

+----+-----------------+----------------+----------------+----------------+----------------+-------------+
| id | address_line_1  | address_line_2 | address_line_3 | address_line_4 | address_line_5 | postal_code |
+----+-----------------+----------------+----------------+----------------+----------------+-------------+
|  1 | Churchill house |                | 30 Boston Road |                | lusaka         | e12 3re     |
+----+-----------------+----------------+----------------+----------------+----------------+-------------+

depending on how you define "shuffle", this may be sufficient:

update @t
set address_line_1 = address_line_2
  , address_line_2 = address_line_3
  , address_line_3 = address_line_4
  , address_line_4 = address_line_5
  , address_line_5 = address_line_1

select * from @t

returns:

+----+----------------+----------------+----------------+----------------+-----------------+-------------+
| id | address_line_1 | address_line_2 | address_line_3 | address_line_4 | address_line_5  | postal_code |
+----+----------------+----------------+----------------+----------------+-----------------+-------------+
|  1 |                | 30 Boston Road |                | lusaka         | Churchill house | e12 3re     |
+----+----------------+----------------+----------------+----------------+-----------------+-------------+

rextester demo: http://rextester.com/WLPAMZ69008

Upvotes: 1

Related Questions