nested tables - 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

nested tables

Author

Topic

cse514
Starting Member

6 Posts

Posted - 2011-08-17 : 04:37:31

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

sir the following one is the reply which was posted but i am getting an error "missing right parenthesis" while i am working with in 10g..
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
please please suggest a solution as early as possible.. i am working for an idea but unfortunately strucked here.My progress is hindered due to this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:40:53

why are you posting same question again and again? did you even try solutions given?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164110
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164136

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:47:45

quote:
Originally posted by cse514

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

sir the following one is the reply which was posted but i am getting an error "missing right parenthesis" while i am working with in 10g..
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
please please suggest a solution as early as possible.. i am working for an idea but unfortunately strucked here.My progress is hindered due to this.

So thats it. you're using ORACLE
this is a SQL Server forum and solutions given here are mostly specific to MS SQL Server.
If you need more help on oracle please try your luck at
www.orafaq.com or www.dbforums.com

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources