Split record into 2 records

[lekfir]

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'

[huangzhi]

Try this code below:

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