Split a record into 2 records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split a record into 2 records

Author

Topic

lekfir
Starting Member

3 Posts

Posted - 2008-10-07 : 16:07:48

Hi,

I am trying to break each record into 2 records but failed

Is it possible? How?
Thanks in advance!!!

For Example:

I have a table that contains 100 records . Each record has the following 10 columns:

Col1, Col2, Col3, Col4, Col5, Col6, Col7

A MT 30 UK 0 70 FR

A SM 30 IL 0 0 PS

And that the result that I want to achieve for the 2 records:

ID JoinID Col1, Col2, Col3, Col4

1 1 Col1 MT UK Yes

2 1 Col3 MT FR No

3 2 Col1 SM IL Yes

4 2 Col3 SM PS Yes

ID - An Incremental id
JoinID - A Join ID to join the 2 records
Col1 - Contains the Col1 title for the first line And Col3 title for the second line.
Col2 - Contains the Col2 value for both lines.
Col3 - Contains the Col4 value for the first line And Col7 value for the second line.
Col4 - If Col5 value = 0 Then the value for the first line is 'Yes' Else 'No' And for the second line, if Col6 value = 0 Then the value 'Yes' Else 'No'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-07 : 16:40:36

Take a look at this link then re-post your question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Basically to create 2 rows for every existing row you just CROSS JOIN to a table with 2 rows. Your new ID column can be done with the ranking function ROW_NUMBER:


select id = row_number() over (order by so.id, d.row) 
       ,so.id JOINID
from   sysobjects so
cross join (select 0 row union all select 1) d
where  so.id < 10

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 00:13:39

[code]declare @test table
(Col1 char(1),
Col2 varchar(3),
Col3 int,
Col4 varchar(3),
Col5 int,
Col6 int,
Col7 varchar(3)
)
insert into @Test
select 'A', 'MT', 30, 'UK', 0, 70, 'FR' union all
select 'A', 'SM', 30, 'IL', 0, 0, 'PS'

select ID,JoinID,CASE WHEN ID %2 = 0 THEN 'Col3' ELSE 'Col1' END AS Col1,
Col2,Col AS Col3,CASE WHEN (ID %2 = 0 AND Col6=0) OR (ID %2 > 0 AND Col5=0) THEN 'Yes' ELSE 'No' END AS Col4
from
(
select dense_rank() over (partition by col1 order by col2) AS JoinID,
row_number() over (partition by col1 order by col2,Val) AS ID,Col2,Col,Col5,Col6
from
(
select * from @test
)m
unpivot (Col FOR Val IN ([Col4],[Col7]))p
)r

output
------------------------------------
ID JoinID Col1 Col2 Col3 Col4
-------------------- -------------------- ---- ---- ---- ----
1 1 Col1 MT UK Yes
2 1 Col3 MT FR No
3 2 Col1 SM IL Yes
4 2 Col3 SM PS Yes[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources