August 21, 2014 at 7:20 am
Hi,
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
Thanks a million!
create table dbo.Movie_Pre_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50)
);
insert into dbo.Movie_Pre_Fix (Report_Month, ID,Movie_Name)
VALUES
(201406,0721312144,'SAW'),
(201406,0521312144,'Need for Speed'),
(201406,0621312144,'300 - Rise of an Empire'),
(201406,0821312144,'Bambi'),
(201406,0726682144,'SAW'),
(201406,0590735121,'300 - Rise of an Empire'),
(201406,0921312144,'Bambi 2'),
(201407,0729527144,'Fast and Furious'),
(201407,0721312144,'Fast and Furious'),
(201406,0721372544,'SAW');
create table dbo.Movie_Post_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50),
Movie_IDint
);
insert into dbo.Movie_Post_Fix(Report_Month, ID,Movie_Name,Movie_ID)
Values
(201406,0721312144,'SAW', 123),
(201406,0521312144,'Need for Speed',7462),
(201406,0621312144,'300 - Rise of an Empire',6241),
(201406,0821312144,'Bambi',2532),
(201406,0726682144,'SAW',123),
(201406,0590735121,'300 - Rise of an Empire',6241),
(201406,0921312144,'Bambi 2',826204),
(201407,0729527144,'Fast and Furious',8725478),
(201407,0721312144,'Fast and Furious',8725478),
(201406,0721372544,'SAW',123);
Select * from Movie_Pre_Fix
Select * from Movie_Post_Fix order by Movie_ID
August 21, 2014 at 7:33 am
I am not sure, but I think I smell a normalization problem here. From the data you have given, it looks like the ID field is interpreted as a Showing ID rather than a Movie ID. Any chance you could make a new table like this:
create table Movies
(MovieID int identity(1, 1) not null,
MovieName varchar(100))
August 21, 2014 at 7:42 am
Agreed 10000% with Matt about normalization. You should have a Movies table and that is the ONLY table where you will find the name. Then you would have a table for Showings. This would include the MovieID and the date it was shown.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 7:51 am
I like your suggestion and thanks for the feedback.
I will create a movie dimension table. I will have a list of the movie text fields. Would you be able to help create a unique key?
create table dbo.Movie_Pre_Fix
(
Movie_NameVarchar(50),
);
insert into dbo.Movie_Pre_Fix(Movie_Name)
Values
('SAW'),
('Need for Speed'),
('300 - Rise of an Empire'),
('Bambi'),
('Bambi 2'),
('Fast and Furious');
create table dbo.Movie_Post_Fix
(
Movie_NameVarchar(50),
Movie_IDint
);
insert into dbo.Movie_Post_Fix(Movie_Name,Movie_ID)
Values
('SAW', 123),
('Need for Speed',7462),
('300 - Rise of an Empire',6241),
('Bambi',2532),
('Bambi 2',826204),
('Fast and Furious',8725478)
Select * from Movie_Pre_Fix
Select * from Movie_Post_Fix order by Movie_ID
August 21, 2014 at 7:57 am
What is prefix and postfix? That should be a single table. And the name of the movie should not be in more than 1 table. That is the point of relational data. If you have the movie name in multiple tables you have to update multiple tables when you realize you spelled it wrong. UGH!!!
Something more like this.
Create table Movie
(
MovieID int identity primary key clustered,
MovieName varchar(50)
)
insert Movie(MovieName)
Values ('SAW'),
('Need for Speed'),
('300 - Rise of an Empire'),
('Bambi'),
('Bambi 2'),
('Fast and Furious')
Select *
from Movie
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 8:07 am
Thanks for the help, you have solved the problem.
Prefix - Was the data before I received help.
PostFix - What I wanted my end solution to look like
August 21, 2014 at 8:30 am
mic.con87 (8/21/2014)
Thanks for the help, you have solved the problem.Prefix - Was the data before I received help.
PostFix - What I wanted my end solution to look like
Ahh now I understand what you meant by those. Glad I was able to offer a solution that works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply