Help with Select query - 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

Help with Select query

Author

Topic

Access
Starting Member

44 Posts

Posted - 2007-03-29 : 15:18:50

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-29 : 15:26:05

Why do you want to denormalize the data like that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Access
Starting Member

44 Posts

Posted - 2007-03-29 : 15:29:34

quote:
Originally posted by X002548

Why do you want to denormalize the data like that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Need to display it in the grid where each word can have multiple synonym but it have to be within one row

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-29 : 16:40:57

I think your table is in 0th Normal Form. Your table is in a master-detail relationship and you need to split it, as far as i know,it's in a denormalized form.My approach would be to..
copy the table into a temp table and than get the output.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:06:28

[code]-- Prepare sample data
DECLARE @Words TABLE (WordID INT, WordName VARCHAR(6), WordSynonym VARCHAR(10))

INSERT @Words
SELECT 1, 'oval', 'egg-shaped' union all
SELECT 2, 'oval', 'rounded' union all
SELECT 3, 'voyage', 'travel' union all
SELECT 4, 'voyage', 'journey' union all
SELECT 5, 'voyage', 'trip'

-- Show the expected output
SELECT DISTINCT
w1.WordName,
STUFF((SELECT TOP 100 PERCENT ',' + w2.WordSynonym FROM @Words AS w2 WHERE w2.WordName = w1.WordName ORDER BY WordID FOR XML PATH('')), 1, 1, '') AS WordSynonyms
FROM @Words AS w1
ORDER BY 1[/code]

Peter Larsson
Helsingborg, Sweden

Access
Starting Member

44 Posts

Posted - 2007-03-30 : 19:37:08

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE	@Words TABLE (WordID INT, WordName VARCHAR(6), WordSynonym VARCHAR(10))

INSERT	@Words
SELECT	1, 'oval', 'egg-shaped' union all
SELECT	2, 'oval', 'rounded' union all
SELECT	3, 'voyage', 'travel' union all
SELECT	4, 'voyage', 'journey' union all
SELECT	5, 'voyage', 'trip'

-- Show the expected output
SELECT		DISTINCT
		w1.WordName,
		STUFF((SELECT TOP 100 PERCENT ',' + w2.WordSynonym FROM @Words AS w2 WHERE w2.WordName = w1.WordName ORDER BY WordID FOR XML PATH('')), 1, 1, '') AS WordSynonyms
FROM		@Words AS w1
ORDER BY	1

Peter Larsson
Helsingborg, Sweden

I gave it a try, but it erroring out.

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-31 : 01:21:50

Do you have SQL Server 2000? Not SQL Server 2005?

Peter Larsson
Helsingborg, Sweden

Access
Starting Member

44 Posts

Posted - 2007-03-31 : 16:04:40

quote:
Originally posted by Peso

Do you have SQL Server 2000? Not SQL Server 2005?

Peter Larsson
Helsingborg, Sweden

No, Jsut SQL Server 2005

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-31 : 16:17:12

This is a presentation issue and should be handled in the front end

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 03:36:09

If you have SQL Server 2005, the code above works.
I am using Developer Edition of SQL Server 2005 and it works great.

Which is the error you get?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources