Split table on columns into two tables.

Question

Split table on columns into two tables.

sandesh.nagaraj

SSC-Addicted

Points: 475
More actions
June 14, 2010 at 3:34 am

#223796

Need a T-sql code to Split table on columns. Please see the Attachment...
Regards,
Sandesh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please provide scripts to create all three tables and populate the source table with data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandesh.nagaraj SSC-Addicted Points: 475 More actions · Answer 2

Changed the example for better understanding of the problem. I need one t-sql statement to do "Split table on columns into two tables" and one t-sql statement to do "loading data into a table with AddrType = Res Or Off "

CREATE TABLE #basetbl

(

Name varchar(25),

Addr varchar(25),

Street varchar(25),

OfficeAddr varchar(10),

OfficeStreet varchar(10),

)

GO

--Insert the following data into the table

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A1','U', 'UUU','A', 'AAA')

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A2','V', 'VVV','B', 'BBB')

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A3','W', 'WWW','C', 'CCC')

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A4','X', 'XXX','D', 'DDD')

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A5','Y', 'YYY','E', 'EEE')

INSERT INTO #basetbl (Name, Addr,Street,OfficeAddr,OfficeStreet) VALUES ('A6','Z', 'ZZZ','F', 'FFF')

SELECT * from #basetbl

GO

-- problem 1 " Split table on columns into two tables"

-- Data inside #tblsplit1

SELECT Name, Addr,Street from #basetbl

-- Data inside #tblsplit2

SELECT Name, OfficeAddr,OfficeStreet from #basetbl

GO

CREATE TABLE #tblsplit1

(

Name varchar(25),

Addr varchar(25),

Street varchar(25),

)

GO

CREATE TABLE #tblsplit2

(

Name varchar(25),

OfficeAddr varchar(10),

OfficeStreet varchar(10),

)

GO

--problem 2 " loading data into a table with AddrType = Res Or Off "

CREATE TABLE #onetbl

(

Name varchar(25),

AddrType varchar(25),

Addr varchar(25),

Street varchar(25),

)

GO

Insert into #onetbl

SELECT Name,'Res', Addr,Street from #basetbl

Insert into #onetbl

SELECT Name,'Off', OfficeAddr,OfficeStreet from #basetbl

select * from #onetbl

-- drop tables

drop table #onetbl

drop table #basetbl

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Nice 🙂

Laying out the table structures and the insert statements has encouraged you to do almost all of the work.

You could answer question 1 like this:

-- problem 1 " Split table on columns into two tables"

-- Data inside #tblsplit1

SELECT Name, Addr,Street

INTO #tblsplit1

from #basetbl

-- Data inside #tblsplit2

SELECT Name, OfficeAddr,OfficeStreet

INTO #tblsplit2

from #basetbl

You could answer the second question with a UNION as follows:

INSERT INTO #onetbl (Name, AddrType, Addr, Street)

SELECT Name,'Res', Addr,Street

FROM #basetbl

UNION ALL

SELECT Name,'Off', OfficeAddr,OfficeStreet

FROM #basetbl

Check BOL for the difference between UNION and UNION ALL.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandesh.nagaraj SSC-Addicted Points: 475 More actions · Answer 4

Thanks.Can we solve the problem 1 with a single t-sql statement?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

sandesh.nagaraj (6/14/2010)
Thanks.Can we solve the problem 1 with a single t-sql statement?

I don't think so, there's only one target of an INSERT INTO or INTO...FROM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandesh.nagaraj SSC-Addicted Points: 475 More actions · Answer 6

sandesh.nagaraj

SSC-Addicted

Points: 475

June 14, 2010 at 7:36 am

#1179853

thanks