elimination of redundancy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

elimination of redundancy

Author

Topic

cse514
Starting Member

6 Posts

Posted - 2011-08-10 : 05:24:33

sir,
i want to eliminate redundant records in tables.
for example i have a table flatrelation having fields course , lady and gentleman. this table is having records of this type shown below
COURSE LADY GENTLEMAN
swing x raaj
swing y raaj
this table contains this sort of records and normal distinct records.
my intention is to eliminate redundacy by doing some processing on the base table i.e flatrelation and converting it as
COURSE LADY GENTLEMAN
swing x,y raaj
that is by making lady as nested.

my sample table is
flatrelation
COURSE LADY GENTLEMAN
swing x raaj
violin abc sai
swing y raaj
veena xyz ramu
violin ccc sai
and my required format is
nestedoverlady
COURSE LADY GENTLEMAN
swing x,y raaj
violin abc,ccc sai
veena xyz ramu

please please reply me with a solution..
thanking you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 07:38:23

[code]INSERT INTO nestedlady
(COURSE, LADY, GENTLEMAN)
SELECT f.COURSE,
STUFF((SELECT ',' + LADY
FROM flatrelation
WHERE COURSE = f.COURSE
AND GENTLEMAN = f.GENTLEMAN
ORDER BY LADY ASC
FOR XML PATH('')),1,1,''),f.GENTLEMAN
FROM (SELECT DISTINCT COURSE,GENTLEMAN FROM flatrelation)f
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-10 : 11:14:10

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> I want to eliminate redundant records [sic] [sic: rows are not records [sic]] in tables. for example I have a table flat relation having fields [sic: columns are not fields] course, lady and gentleman. This table is having records [sic] [sic] of this type shown below <<

If you are not going to follow basic Netiquette, can you at least get the basic RDBMS terms right? Here is what you should have done.

CREATE TABLE Dance_Partners
(course_name VARCHAR(15) NOT NULL
REFERENCES Courses(course_name)
ON DELETE CASCADE,
lady_name VARCHAR(15) NOT NULL
REFERENCES Ladies(lady_name)
ON DELETE CASCADE,
gentleman_name VARCHAR(15) NOT NULL
REFERENCES Gentlemen(gentleman_name)
ON DELETE CASCADE,,
PRIMARY KEY (course_name, lady_name, gentleman_name),
etc.);

Dance partnerships are relationships among many entities and can have attributes of its own (date, place, etc.)

>> this table contains this sort of records [sic] and normal distinct records [sic]. My intention is to eliminate redundancy by doing some processing on the base table <<

This makes no sense. In the relational model, all values are scalar, not lists. That is one many ways that a column is not a field. You want to destroy First Normal Form (1NF)! That is the foundation of RDBMS.

Either read a book on RDBMS or write a paper proving that you are smarter than Dr. Codd and that the whole relational model is wrong. If you like needless overhead and the nightmare of maintaining a mixed system, you can use an XML kludge.

I would pick the book, myself :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

cse514
Starting Member

6 Posts

Posted - 2011-08-10 : 14:33:33

sir i typed the query in the similar fashion you gave to me but it is throwing an error "missing parenthesis".. i created a nested table also by making lady as nested but i am not getting the required output instead an error is arising.. please please reply me with a solution.
thanking you for providing me the valuable information which i needed the most..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 08:26:50

can you show what query you used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cse514
Starting Member

6 Posts

Posted - 2011-08-13 : 02:21:27

INSERT INTO nestedlady
(COURSE, LADY, GENTLEMAN)
SELECT f.COURSE,
STUFF((SELECT ',' + LADY
FROM flatrelation
WHERE COURSE = f.COURSE
AND GENTLEMAN = f.GENTLEMAN
ORDER BY LADY ASC
FOR XML PATH('')),1,1,''),f.GENTLEMAN
FROM (SELECT DISTINCT COURSE,GENTLEMAN FROM flatrelation)f
this is the query i used and i am getting an error missing right paranthesis .. i can't understand the reason for that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources