repeat - 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)

repeat

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-13 : 09:12:22

There is a table i.e. table1 with fields ID, field1, field2, name, name2
fields name and name1 really refer to the same thing.
So if a record has the field name2 populated, then I would like to have the same record repeated so that the first record has the field name populated and not the field name2 and the second record has the field name2 populated and not the field name. Everything else for these records will be repeated identically.

Thank you

example:

create table #table1
(
ID int identity(1, 1) , field1 varchar(20), field2 varchar(20), name varchar(20), name2 varchar(20)
)

insert into #table1 (field1, field2, name, name2)
select 'field1', 'field2', 'peter', 'pete'
union
select 'field11', 'field22', 'jackson', 'jack'
union
select 'field111', 'field222', 'Robert', 'rob'

select * from #table1

truncate table #table1

--RESULT
insert into #table1 (field1, field2, name, name2)
select 'field1', 'field2', 'peter', null
union
select 'field1', 'field2', null, 'pete'

union
select 'field11', 'field22', 'jackson', null
union
select 'field11', 'field22', null, 'jack'

union
select 'field111', 'field222', 'Robert', null
union
select 'field111', 'field222', null, 'rob'

select * from #table1

drop table #table1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-13 : 09:19:57

I can do this using union but is union the only solution to this ?
Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources