dbTalk Databases Forums  

SSIS - combine two rows into one

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts SSIS - combine two rows into one

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss SSIS - combine two rows into one in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Jason
 
Posts: n/a

Default SSIS - combine two rows into one - 08-02-2006 , 08:40 AM





I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule table
using the two rows in the Excel spreadsheet. I basically have to get the two
rows in Excel and convert into one row in the table. I then have to use the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?

Reply With Quote
  #2  
Old   
Allan Mitchell
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-02-2006 , 10:07 AM





So can you identify the 2 rows that are linked?

I would be inclined to use the Merge Join Transofrm to start with

I would have two sources of the same Spreadsheet

I would link on keys

I would then only return the columns from the relevant side of the join

If this does not do it for you then you can always look at a Script
Component as a transform.

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Jason" <Jason (AT) discussions (DOT) microsoft.com> wrote

Quote:
I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule
table
using the two rows in the Excel spreadsheet. I basically have to get the
two
rows in Excel and convert into one row in the table. I then have to use
the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?



Reply With Quote
  #3  
Old   
Jason
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-02-2006 , 10:26 AM


The rows are sequential. The only way to identify the rows is to start from
the top and grab the row with the A in the Away/Home column and then grab the
row with the H. This makes up the one row in the table. After that then
move to rows 3 & 4.

"Allan Mitchell" wrote:

Quote:
So can you identify the 2 rows that are linked?

I would be inclined to use the Merge Join Transofrm to start with

I would have two sources of the same Spreadsheet

I would link on keys

I would then only return the columns from the relevant side of the join

If this does not do it for you then you can always look at a Script
Component as a transform.

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Jason" <Jason (AT) discussions (DOT) microsoft.com> wrote in message
news:4CC3FDF7-FF06-4CA5-B803-6DA9B6AD2121 (AT) microsoft (DOT) com...
I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule
table
using the two rows in the Excel spreadsheet. I basically have to get the
two
rows in Excel and convert into one row in the table. I then have to use
the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?

Reply With Quote
  #4  
Old   
sqlboy2000
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-02-2006 , 02:09 PM


More than likely, you'll have to import the spreadsheet into a temp table,
then loop through each row using a cursor. Each time you hit a row with an
"A", you'll then grab the next row and do your insert. Hopefully this is a
one time import as cursors run very slowly. If you haven't used them before,
see books on line for some cursor examples. Here's some utterly untested code
that assumes your rows will always be in the order A first, then H. Hopefully
it gets you going.

declare @AwayTeam varchar(100), @HomeTeam varchar(100), @RowType char(1),
@TeamName varchar(100)

declare test cursor for select * from spreadsheet
open test
fetch next from test into @RowType, @TeamName
while @@fetch_status = 0
begin
if @RowType = 'A'
begin
select @AwayTeam = @TeamName
fetch next from test into @rowtype, @TeamName
select @HomeTeam = @TeamName
insert into TargetTable
VALUES
(@HomeTeam, @AwayTeam)
END
fetch next from test into @rowType, @TeamName
END

close test
deallocate test





"Jason" wrote:

Quote:
The rows are sequential. The only way to identify the rows is to start from
the top and grab the row with the A in the Away/Home column and then grab the
row with the H. This makes up the one row in the table. After that then
move to rows 3 & 4.

"Allan Mitchell" wrote:

So can you identify the 2 rows that are linked?

I would be inclined to use the Merge Join Transofrm to start with

I would have two sources of the same Spreadsheet

I would link on keys

I would then only return the columns from the relevant side of the join

If this does not do it for you then you can always look at a Script
Component as a transform.

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Jason" <Jason (AT) discussions (DOT) microsoft.com> wrote in message
news:4CC3FDF7-FF06-4CA5-B803-6DA9B6AD2121 (AT) microsoft (DOT) com...
I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule
table
using the two rows in the Excel spreadsheet. I basically have to get the
two
rows in Excel and convert into one row in the table. I then have to use
the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?

Reply With Quote
  #5  
Old   
Jason
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-03-2006 , 08:02 AM


Thanks, I have not used cursors before but there is always a first for
everything. Yes, this is a one-time import and the records are in order.

"sqlboy2000" wrote:

Quote:
More than likely, you'll have to import the spreadsheet into a temp table,
then loop through each row using a cursor. Each time you hit a row with an
"A", you'll then grab the next row and do your insert. Hopefully this is a
one time import as cursors run very slowly. If you haven't used them before,
see books on line for some cursor examples. Here's some utterly untested code
that assumes your rows will always be in the order A first, then H. Hopefully
it gets you going.

declare @AwayTeam varchar(100), @HomeTeam varchar(100), @RowType char(1),
@TeamName varchar(100)

declare test cursor for select * from spreadsheet
open test
fetch next from test into @RowType, @TeamName
while @@fetch_status = 0
begin
if @RowType = 'A'
begin
select @AwayTeam = @TeamName
fetch next from test into @rowtype, @TeamName
select @HomeTeam = @TeamName
insert into TargetTable
VALUES
(@HomeTeam, @AwayTeam)
END
fetch next from test into @rowType, @TeamName
END

close test
deallocate test





"Jason" wrote:

The rows are sequential. The only way to identify the rows is to start from
the top and grab the row with the A in the Away/Home column and then grab the
row with the H. This makes up the one row in the table. After that then
move to rows 3 & 4.

"Allan Mitchell" wrote:

So can you identify the 2 rows that are linked?

I would be inclined to use the Merge Join Transofrm to start with

I would have two sources of the same Spreadsheet

I would link on keys

I would then only return the columns from the relevant side of the join

If this does not do it for you then you can always look at a Script
Component as a transform.

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Jason" <Jason (AT) discussions (DOT) microsoft.com> wrote in message
news:4CC3FDF7-FF06-4CA5-B803-6DA9B6AD2121 (AT) microsoft (DOT) com...
I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule
table
using the two rows in the Excel spreadsheet. I basically have to get the
two
rows in Excel and convert into one row in the table. I then have to use
the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?

Reply With Quote
  #6  
Old   
Jason
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-05-2006 , 09:17 AM


Just a bit more complexity to this that I didn't include in my first post.
While adding the schedule into the schedule table, I need to add the just
entered scheduleid into the GameScores table along with the scores of the
game. Will a cursor handle this too?

I brought the Excel spreadsheet into a table thinking that might be easier
to deal with. Here is the create table scripts and sample data:

CREATE TABLE [dbo].[Temp](
[year] [float] NULL,
[week] [float] NULL,
[teams] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[away/home] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[score] [float] NULL,
[first downs] [float] NULL,
[# rushes] [float] NULL,
[rushing yds] [float] NULL,
[passing yds] [float] NULL,
[p completions] [float] NULL,
[p attempts] [float] NULL,
[interceptions] [float] NULL,
[return yds] [float] NULL,
[# sacks allwd] [float] NULL,
[sack yds lost] [float] NULL,
[# punts] [float] NULL,
[avg# punt] [float] NULL,
[# fumbles] [float] NULL,
[# fmbls lost] [float] NULL,
[# penalties] [float] NULL,
[pen yds lost] [float] NULL,
[time of possession] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[quarter 1] [float] NULL,
[quarter 2] [float] NULL,
[quarter 3] [float] NULL,
[quarter 4] [float] NULL,
[over-under / pointspread] [float] NULL,
[day of week / overtime?] [nvarchar](255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[minutes] [int] NULL,
[seconds] [int] NULL,
[new time] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id] [int]
) ON [PRIMARY]


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home],
[score],[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks allwd],
[sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls lost], [#
penalties], [pen yds lost], [time of possession], [quarter 1],[quarter 2],
[quarter 3], [quarter 4], [over-under / pointspread], [day of week /
overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'NO', N'A', 13, 8, 16, 55, 147, 12, 30, 2, 10, 1, 9,
6, 45, 3, 0, 7, 61, '1899-12-30', 3, 3, 0, 7, 34.5, N'SU', 22, 15,
N'22:15', 1)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home],
[score],[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks allwd],
[sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls lost], [#
penalties], [pen yds lost], [time of possession], [quarter 1],[quarter 2],
[quarter 3], [quarter 4], [over-under / pointspread], [day of week /
overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'PHI', N'H', 15, 21, 40, 186, 134, 18, 25, 0, 79, 6,
31, 5, 43, 5, 4, 3, 30, '1900-01-01', 6, 3, 0, 6, -3, NULL, 37, 45,
N'37:45 ', 2)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home],
[score],[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks allwd],
[sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls lost], [#
penalties], [pen yds lost], [time of possession], [quarter 1],[quarter 2],
[quarter 3], [quarter 4], [over-under / pointspread], [day of week /
overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'LA', N'A', 7, 15, 22, 66, 149, 19, 38, 4, 5, 3, 21,
6, 43, 2, 0, 7, 60, '1900-01-01', 0, 7, 0, 0, 44.5, N'SU', 30, 11,
N'30:11 ', 3)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home],
[score],[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks allwd],
[sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls lost], [#
penalties], [pen yds lost], [time of possession], [quarter 1],[quarter 2],
[quarter 3], [quarter 4], [over-under / pointspread], [day of week /
overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'BUF', N'H', 40, 23, 33, 207, 156, 19, 30, 1, 157, 2,
9, 4, 42, 2, 0, 12, 75, '1900-01-01', 14, 13, 7, 6, -11, NULL, 29, 49,
N'29:49 ', 4)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home],
[score],[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks allwd],
[sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls lost], [#
penalties], [pen yds lost], [time of possession], [quarter 1], [quarter 2],
[quarter 3], [quarter 4], [over-under / pointspread], [day of week /
overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'DET', N'A', 24, 17, 23, 121, 233, 18, 26, 1, 6, 6,
40, 5, 43, 2, 0, 8, 44, '1900-01-01', 0, 10, 0, 14, 36.5, N'SU', 29, 9,
N'29:9 ', 5)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p completions], [p
attempts], [interceptions], [return yds], [# sacks allwd], [sack yds lost],
[# punts], [avg# punt], [# fumbles], [# fmbls lost], [# penalties], [pen yds
lost], [time of possession], [quarter 1], [quarter 2], [quarter 3], [quarter
4], [over-under / pointspread], [day of week / overtime?], [minutes],
[seconds], [new time], [id])
VALUES (1992, 1, N'CHI', N'H', 27, 21, 29, 140, 227, 19, 30, 0, 85, 0,
0, 4, 43, 4, 0, 7, 55, '1900-01-01', 7, 3, 0, 17, -3.5, NULL, 30, 51,
N'30:51 ', 6)

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[Day] [smallint] NULL,
[Time] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[GameScores](
[ScheduleId] [int] NOT NULL,
[HScore] [int] NULL,
[AScore] [int] NULL,
[HQtr1] [int] NULL,
[HQtr2] [int] NULL,
[HQtr3] [int] NULL,
[HQtr4] [int] NULL,
[HOT] [int] NULL,
[AQtr1] [int] NULL,
[AQtr2] [int] NULL,
[AQtr3] [int] NULL,
[AQtr4] [int] NULL,
[AOT] [int] NULL,
[Overtime] [bit] NULL,
CONSTRAINT [PK_GameScores] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


"sqlboy2000" wrote:

Quote:
More than likely, you'll have to import the spreadsheet into a temp table,
then loop through each row using a cursor. Each time you hit a row with an
"A", you'll then grab the next row and do your insert. Hopefully this is a
one time import as cursors run very slowly. If you haven't used them before,
see books on line for some cursor examples. Here's some utterly untested code
that assumes your rows will always be in the order A first, then H. Hopefully
it gets you going.

declare @AwayTeam varchar(100), @HomeTeam varchar(100), @RowType char(1),
@TeamName varchar(100)

declare test cursor for select * from spreadsheet
open test
fetch next from test into @RowType, @TeamName
while @@fetch_status = 0
begin
if @RowType = 'A'
begin
select @AwayTeam = @TeamName
fetch next from test into @rowtype, @TeamName
select @HomeTeam = @TeamName
insert into TargetTable
VALUES
(@HomeTeam, @AwayTeam)
END
fetch next from test into @rowType, @TeamName
END

close test
deallocate test





"Jason" wrote:

The rows are sequential. The only way to identify the rows is to start from
the top and grab the row with the A in the Away/Home column and then grab the
row with the H. This makes up the one row in the table. After that then
move to rows 3 & 4.

"Allan Mitchell" wrote:

So can you identify the 2 rows that are linked?

I would be inclined to use the Merge Join Transofrm to start with

I would have two sources of the same Spreadsheet

I would link on keys

I would then only return the columns from the relevant side of the join

If this does not do it for you then you can always look at a Script
Component as a transform.

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Jason" <Jason (AT) discussions (DOT) microsoft.com> wrote in message
news:4CC3FDF7-FF06-4CA5-B803-6DA9B6AD2121 (AT) microsoft (DOT) com...
I have an Excel spreadsheet that contains a history of football games which
is in this format:

Year | Week | Team | Away/Home | Various stats for each team.
1992 1 NO A
1992 1 PHI H
1992 1 DET A
1992 1 CHI H

I would like to get it in a relational format to do analysis so that
requires me to create two tables (Schedule and GameStats).

These are the two tables in which I would like to import into:

CREATE TABLE [dbo].[Schedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Week] [int] NULL,
[HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GameStats](
[ScheduleId] [int] NOT NULL,
[HFirstDowns] [int] NULL,
[HRushes] [int] NULL,
[HRushingYds] [int] NULL,
[HPassingYds] [int] NULL,
[HPassCompletions] [int] NULL,
[HPassAttempts] [int] NULL,
[HInts] [int] NULL,
[HReturnYds] [int] NULL,
[HSacksAllowed] [int] NULL,
[HSacksYdsLost] [int] NULL,
[HPunts] [int] NULL,
[HAvgPunt] [int] NULL,
[HFumbles] [int] NULL,
[HFumblesLost] [int] NULL,
[HPenalties] [int] NULL,
[HPenaltyYdsLost] [int] NULL,
[HTimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AFirstDowns] [int] NULL,
[ARushes] [int] NULL,
[ARushingYds] [int] NULL,
[APassingYds] [int] NULL,
[APassCompletions] [int] NULL,
[APassAttempts] [int] NULL,
[AInts] [int] NULL,
[AReturnYds] [int] NULL,
[ASacksAllowed] [int] NULL,
[ASacksYdsLost] [int] NULL,
[APunts] [int] NULL,
[AAvgPunt] [int] NULL,
[AFumbles] [int] NULL,
[AFumblesLost] [int] NULL,
[APenalties] [int] NULL,
[APenaltyYdsLost] [int] NULL,
[ATimeOfPossession] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am having troubles with trying to create the one row in the Schedule
table
using the two rows in the Excel spreadsheet. I basically have to get the
two
rows in Excel and convert into one row in the table. I then have to use
the
newly inserted ScheduleId and do the same for the GameStats. Can someone
help me with this (tutorial or other links)?

Reply With Quote
  #7  
Old   
AT
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-07-2006 , 07:07 AM


Hi Jason,
I'm researching on this issue and will give you reply later.

Charles Wang
Microsoft Online Community Support


Reply With Quote
  #8  
Old   
Jason
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-07-2006 , 08:46 AM


Charles,

I was able to import the data but it was with several Transact-SQL
statements. No need to research.

Thanks,
Jason

"Charles Wang[MSFT]" wrote:

Quote:
Hi Jason,
I'm researching on this issue and will give you reply later.

Charles Wang
Microsoft Online Community Support

Reply With Quote
  #9  
Old   
AT
 
Posts: n/a

Default Re: SSIS - combine two rows into one - 08-07-2006 , 08:54 PM


Hi Jason,

Appreciate your update and response. I am glad to hear that the problem has
been fixed.
If you have any other questions or concerns, please do not hesitate to
contact us.

Have a nice day!

Charles Wang
Microsoft Online Community Support


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top