WOW!!! Thanks SO MUCH Tom. That worked great!!!
I do always have questions though, because I try so hard to understand.
Why does it produce the
message above the
(2 row(s) affected)
When I run a select statement on both tables I still see the NULL values where
they should be.
ALSO, As I read through and study this query to try to understand it and how it
works, can you provide any further guidance on some of commands you used, such
as ROW_NUMBER() OVER(PARTITION BY SID ORDER BY SID, NAME) RW, the SQL2005 help
is not very detailed
AND, each CASE statement is a simple statement, could these be IF statements ??
Please don't be offended by my stupidity!! I truly appreciate all your
assistance !!!
Thank You!!!
=====================================
Here's a SQL 2005 solution:
with x
as
(
select
sID
, Name
, row_number () over (partition by sID order by sID, Name) rw
from
table2
)
insert table1
select
sID
, min (case when rw = 1 then Name end)
, min (case when rw = 2 then Name end)
, min (case when rw = 3 then Name end)
, min (case when rw = 4 then Name end)
, min (case when rw = 5 then Name end)
, min (case when rw = 6 then Name end)
from
x
group by
sID
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
Thanks Tom, But this inserts data from Flat file into Relational File and I
am
trying to parse the data from the relational table into the flat file table,
sorry if my explanation wasn't clear.
=======================================================
Thanx you for supplying DDL and data. Here's a solution, using a technique
known as "unpivoting", which will work in SQL 2000 as well as SQL 2005:
insert table2 (sID, Name)
select
sID
, Name
from
(
select
t1.sID
, case x.pos
when 1 then t1.Name1
when 2 then t1.Name2
when 3 then t1.Name3
when 4 then t1.Name4
when 5 then t1.Name5
when 6 then t1.Name6
end Name
from
(
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
) as x (pos)
cross join
table1 t1
) y
where
y.Name is not null
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
I would like to direct this to someone who helped me on a previous query,
but I
am not sure if that violates some kind of etiquette, Anyway, I Hope you know
who
you are, You were a big help, thanks again!!
I would REALLY appreciate any help. I hope that with just a little more
help I
should be able to work through the many more I need to complete.
How can the data in a relational table be parsed into a flat file table??
For instance; table1 has an id for each entry and an sid and name, table2
has an
sid and name1 name2 name3...456.
I have both tables in the same database and want to import the data from
table1
into table2. The records in table1 with identical sid's should create only
one
record in table2. Here is a sample of the tables, and what I have started
with>>
CREATE DATABASE SAMPLE002
GO
USE SAMPLE002
CREATE TABLE table1
(
sID int NOT NULL,
Name1 varchar(50) NULL,
Name2 varchar(50) NULL,
Name3 varchar(50) NULL,
Name4 varchar(50) NULL,
Name5 varchar(50) NULL,
Name6 varchar(50) NULL
)
GO
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(1,
'Hal', 'Jenny', 'Hank', 'Mary', NULL, NULL)
INSERT INTO table1 (sID, Name1, Name2, Name3, Name4, Name5, Name6) VALUES
(2,
'Leroy', 'Anne', 'Curtis', NULL, NULL, NULL)
GO
CREATE TABLE table2
(
pID int IDENTITY (1, 1) NOT NULL,
sID int NOT NULL,
Name varchar(50) NOT NULL
)
GO
INSERT INTO table2 (sID, Name) VALUES (3, 'Robert')
INSERT INTO table2 (sID, Name) VALUES (3, 'Roger')
INSERT INTO table2 (sID, Name) VALUES (3, 'Edgar')
INSERT INTO table2 (sID, Name) VALUES (3, 'Mitch')
INSERT INTO table2 (sID, Name) VALUES (4, 'Julie')
INSERT INTO table2 (sID, Name) VALUES (4, 'Wendy')
INSERT INTO table2 (sID, Name) VALUES (4, 'Roberto')
--View the tables as they are
select * from table1
select * from table2
--I tried this first but of course it duplicates the sid in table 2 and that
can't happen. I expect there needs to be a looping process on each sid but
I am
not sure how to write that
insert into table1 (sid,Name1)
select sid,Name from table2
--Use this to clean out the mess made by the previous insert
delete from table1
where sid >2
THANK YOU !!!