MS SQL

MS SQL RSS

Split record into 2 records

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Oct 2008

Posts: 2

Reputation: lekfir is an unknown quantity at this point

Solved Threads: 0

lekfir

Offline

Newbie Poster

Split record into 2 records

Oct 9th, 2008

Hi,
I am trying to break each record into 2 records but failed
Is it possible?
What is the correct way to do it?
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'

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: Split record into 2 records

Oct 12th, 2008

Try this code below:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT 1 AS Col1, 'MT' AS Col2, 30 AS Col3, 'UK' AS Col4, 0 AS Col5, 70 AS Col6, 'FR' AS Col7
  INTO #tmpX
union ALL
SELECT 2 AS Col1, 'SM' AS Col2, 30 AS Col3, 'IL' AS Col4, 0 AS Col5,  0 AS Col6, 'PS' AS Col7
 
 
SELECT Identity(int, 1, 1) AS ID, * 
  INTO #tmpResult
  FROM (
	SELECT Col1 AS JoinID, 'Col1' AS Col1, Col2, Col4 AS Col3, case when Col5 = 0 then 'Yes' else 'No' end AS Col4
	  FROM #tmpX
	union ALL
	SELECT Col1 AS JoinID, 'Col3' AS Col1, Col2, Col4 AS Col3, case when Col6 = 0 then 'Yes' else 'No' end AS Col4
	  FROM #tmpX
	) X
  ORDER BY JoinID
 
SELECT * FROM #tmpResult
 
DROP TABLE #tmpResult
DROP TABLE #tmpX
select 1 as Col1, 'MT' as Col2, 30 as Col3, 'UK' as Col4, 0 as Col5, 70 as Col6, 'FR' as Col7
  into #tmpX
union all
select 2 as Col1, 'SM' as Col2, 30 as Col3, 'IL' as Col4, 0 as Col5,  0 as Col6, 'PS' as Col7


select Identity(int, 1, 1) as ID, * 
  into #tmpResult
  from (
	select Col1 as JoinID, 'Col1' as Col1, Col2, Col4 as Col3, case when Col5 = 0 then 'Yes' else 'No' end as Col4
	  from #tmpX
	union all
	select Col1 as JoinID, 'Col3' as Col1, Col2, Col4 as Col3, case when Col6 = 0 then 'Yes' else 'No' end as Col4
	  from #tmpX
	) X
  order by JoinID

select * from #tmpResult

drop table #tmpResult
drop table #tmpX