CODEWITHSUNDEEP
sqlsql-server
Niks
Niks

Reputation: 110

How to split a row in multiple rows SQL Server?

I want to convert a row in sql table to multiple rows in other table. example: say if i'm having a table 'UserDetail' and he has 2 address's (home, office...) then the table looks like...

enter image description here

I wand the result to be in the second table as shown in the image

Upvotes: 0

Views: 466

Answers (3)

LukStorms
LukStorms

Reputation: 29677

You can CROSS JOIN or CROSS APPLY the table to a list of numbers.
Then use IIF or CASE to get the correspondent numbered fields.

Or CROSS APPLY on the address values directly on the table.

Example snippet: 

declare @UserTable table (UserId int, Address1Line varchar(30), Address1City varchar(30), Address1State varchar(30), Address2Line varchar(30), Address2City varchar(30), Address2State varchar(30));

insert into @UserTable (UserId, Address1Line, Address1City, Address1State, Address2Line, Address2City, Address2State) values 
(1,'Wonder Lane 42','WonderTown', 'WonderState', 'Somewhere 1 B', 'Nowhere', 'Anywhere'),
(2,'Backstreet 69','Los Libros', 'Everland', 'Immortal Cave 777', 'Ghost City', 'The Wild Lands');

-- Cross Join on numbers
select UserId, 
case n when 1 then Address1Line when 2 then Address2Line end as [Address],
case n when 1 then Address1City when 2 then Address2City end as [City],
case n when 1 then Address1State when 2 then Address2State end as [State]
from @UserTable u
cross join (values (1),(2)) as nums(n);

-- Cross Apply on Adress values
select UserId, [Address], [City], [State]
from @UserTable Usr
cross apply (values 
 (1, Address1Line, Address1City, Address1State),
 (2, Address2Line, Address2City, Address2State)
) AS Addr(n, [Address], [City], [State]);

Both return: 

UserId Address           City       State
------ ----------------- ---------- --------------
1      Wonder Lane 42    WonderTown WonderState
1      Somewhere 1 B     Nowhere    Anywhere
2      Backstreet 69     Los Libros Everland
2      Immortal Cave 777 Ghost City The Wild Lands

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23867

You could use "union all" to do that like:

select * into newTable
from
(
select UserId, Address1Line as Address, Address1City as City, Address1State as State
from myTable
union all
select UserId, Address2Line as Address, Address2City as City, Address2State as State
from myTable
) tmp

If you use just UNION instead of UNION ALL you would also be removing the duplicates where Address1 and Address2 is same.

Upvotes: 3

Sreenu131
Sreenu131

Reputation: 2526

We can use Cross Apply

;WITH CTE(UseriD,Address1Line,Address1City,Address1State,Address2Line,Address2City,Address2State )
AS
(
SELECT 1,'Line1','City1','State1','Line2','City2','State2'
)
SELECT UseriD,[Address],City,[State]
FROM CTE
CROSS APPLY ( VALUES (Address1Line,Address1City,Address1State ),
                     (Address2Line,Address2City,Address2State )
            )AS Dt([Address],City,[State])

Result

UseriD  Address City    State
-----------------------------
1       Line1   City1   State1
1       Line2   City2   State2

Demo:http://rextester.com/KHFUM28227

Upvotes: 3

Related Questions