Split record into 2 records

Expand Post »

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'

Similar Threads

fstream Tutorial in C++
seeming limit on number of shelve records? in Python
VB6 - Excel display error: "#######" in Visual Basic 4 / 5 / 6
Updating a file: setting the offset in Perl
Saving Entered Data in C++

lekfir

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Oct 2008

Permalink

Oct 12th, 2008

Re: Split record into 2 records

Try this code below:

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Codings

Next Thread in MS SQL Forum Timeline: Error converting data type varchar to datetime

DaniWeb Message

Cancel Changes

User Name
Password