sql server new users: 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 !!!

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


[quoted text, click to view]
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 !!!

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.
=======================================================
[quoted text, click to view]
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


[quoted text, click to view]
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 !!!

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


[quoted text, click to view]
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.
=======================================================
[quoted text, click to view]
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


[quoted text, click to view]
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 !!!

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
"WARNING: Null value is eliminated by an aggregate or other SET operation."
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!!!
=====================================
[quoted text, click to view]
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


[quoted text, click to view]
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.
=======================================================
[quoted text, click to view]
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


[quoted text, click to view]
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 !!!

[quoted text, click to view]

That's because these lines:

[quoted text, click to view]

all produce a vlaue of either Name or NULL (if there is no ELSE in a
CASE expression, it defaults to ELSE NULL), The MIN() function removes
these values again, but according to ANSI specifications, a warning must
be given that NULL values were omitted in an aggregate.

Or the short answer - that is expected behaviour for this query.


[quoted text, click to view]

What this does (logically - the physical implementation might be
different as long as the results are the same) is:
1. Group rows in the result set on the SID column. I.e., for each SID
value, you'll get a group with all the rows with that SID value..
2. Within each group, order the rows by SID and Name. Of course, since
SID is always equal within each group, Tom could also have used
(PARTITION BY SID ORDER BY NAME)
3. Number the rows sequentially, respecting the order, and restarting
from 1 for each SID group.
4. Make this logical row number available to the outer query under the
alias RW (Tom omitted the optional keyword AS before the alias RW, which
I always include for extra clarity)


[quoted text, click to view]

In SQL Server, CASE is an expression, not a statement. That means that
you can't execute it by itself, but can include it in a query. It will
be evaluated for each row, and the value of the expression depends on
that evaluation.


[quoted text, click to view]

My motto is that there are no stupid questions. Not asking a question,
that is stupid. <g>

--
Hugo Kornelis, SQL Server MVP

Thank you all for all your help, and guidance!!
Someday I hope that I can be as helpful to someone else, as you have all been to
me!
WANNABE
"I wannabe an MVP, all that and more"
===================================
[quoted text, click to view]

That's because these lines:

[quoted text, click to view]

all produce a vlaue of either Name or NULL (if there is no ELSE in a
CASE expression, it defaults to ELSE NULL), The MIN() function removes
these values again, but according to ANSI specifications, a warning must
be given that NULL values were omitted in an aggregate.

Or the short answer - that is expected behaviour for this query.


[quoted text, click to view]

What this does (logically - the physical implementation might be
different as long as the results are the same) is:
1. Group rows in the result set on the SID column. I.e., for each SID
value, you'll get a group with all the rows with that SID value..
2. Within each group, order the rows by SID and Name. Of course, since
SID is always equal within each group, Tom could also have used
(PARTITION BY SID ORDER BY NAME)
3. Number the rows sequentially, respecting the order, and restarting
from 1 for each SID group.
4. Make this logical row number available to the outer query under the
alias RW (Tom omitted the optional keyword AS before the alias RW, which
I always include for extra clarity)


[quoted text, click to view]

In SQL Server, CASE is an expression, not a statement. That means that
you can't execute it by itself, but can include it in a query. It will
be evaluated for each row, and the value of the expression depends on
that evaluation.


[quoted text, click to view]

My motto is that there are no stupid questions. Not asking a question,
that is stupid. <g>

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis