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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 possible to return Unique/Distinct row

Author  Topic 

asdoye111
Starting Member

16 Posts
Posted - 2009-06-25 : 15:01:01
this isn't exactly the same question that was in the other threads i don't think. let's say i have this bit of code:


Select I.ACode, I.ADescription + '/' + I.BCode + '-' + I.BDescription + '/' as AString,
I.CCode + '-' + I.CDescription + '/' + I.DCode + '-' + I.DDescription + '/' + I.ECode + '-' + I.EDescription as BString,
I.FID
From table_01 I

This will display a table with something similar to this:

ACode| AString | B String | FID
1111 | x-XX/x-XX|x-ab/x-cd | ABC
1111 | x-XX/x-XX|x-ef/x-gh | ABC
1112 | y-YY/y-YY|y-ab/y-cd | ABC
1113 | z-ZZ/z-ZZ|z-ab/z-cd | ABC
1113 | z-ZZ/z-ZZ|z-ef/z-gh | ABC

is there a way to just display 1 instance of Astring, like so:

ACode| AString | B String | FID
1111 | x-XX/x-XX|x-ab/x-cd | ABC
1111 | |x-ef/x-gh | ABC
1112 | y-YY/y-YY|y-ab/y-cd | ABC
1113 | z-ZZ/z-ZZ|z-ab/z-cd | ABC
1113 | |z-ef/z-gh | ABC


i assume no. but for display purposes i'd like my table to look like that if i could...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-06-25 : 15:16:33
This probably isn't 100% correct, but it might get your going: 
Select 
	I.ACode, 
	CASE
		WHEN RowNum = 1 THEN I.ADescription + '/' + I.BCode + '-' + I.BDescription + '/' 
		ELSE ''
	END as AString,
	I.CCode + '-' + I.CDescription + '/' + I.DCode + '-' + I.DDescription + '/' + I.ECode + '-' + I.EDescription as BString,
	I.FID
FROM
	(
		SELECT 	
			ACode, 
			ADescription,
			BCode,
			BDescription,
			CCode,
			CDescription,
			DCode,
			DDescription,
			ECode,
			EDescription,
			FID,
			ROW_NUMBER() OVER (PARTITION BY ADescription + '/' + BCode + '-' + BDescription + '/' ORDER BY ACode) AS RowNum
		FROM 
			table_01
	) AS I
Go to Top of Page

asdoye111
Starting Member

16 Posts
Posted - 2009-06-25 : 15:54:50
my text didn't come out very clear, but i'm gonna see if your idea worked. i'll let you know
Go to Top of Page

asdoye111
Starting Member

16 Posts
Posted - 2009-06-25 : 16:01:38
holy poop on a stick! it worked. Thanks so much!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site