Distinct and Group By.......????

[newmediaguy]

Hi Guys

I am having trouble getting my head round writing a query to producte the results i need.

What i currently have is a table in the following format:-

ID Cat_ID Man_ID UName UEmail DistributorID
011 10 23 Glen glen@glen 23,250,10,103
012 25 45 chris chris@chris 106,25,419

The distributor is a column that is the friendly key for another table. I will then be able to return the values from the distributor table (DistributorID = distributor.ID)

I have never split values in a column and then tried to use them......

I need to produce the following results

Cat ID Man ID Uname Uemail distributor
11 23 Glen glen@ 23
11 23 Glen glen@ 250
11 23 Glen glen@ 10
11 23 Glen glen@ 103

So spliting out the distributor column for each unique distributor id, which will always seperated by a comma (it is inserted as an array)

Any thoughts would be appreciated.

Many thanks

Glen

 

[jgoodman00]

I think you will have difficulty.

This structure is not normalized, & as such any solution will be a bodge at best.

You would be better seperating the Distributor column into a seperate table, 1-M...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[newmediaguy]

Thanks James,

I was really clutching at straws....... I will remake the part of the app and as you said create a table that joins them.

Appreciate the feedback.

Many thanks

Glen

 

[jgoodman00]

Also, look at the other columns:

Are CatID & ManID related in any way?

How do UName & UEmail relate to the above?


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[newmediaguy]

Hi James

Cat ID is the ID of categories in the categories tbl, Man Id is the manufacturers ID in manufacturers tbl.

Cat ID 10 = Backup
Man ID 412 = Seagate

Username is the name of a user who has specified the cat and man, email is the email of that user.

I hope makes sense :S

Glen

 

[Katy44]

If your table is quite small you may be able to export to Exceland then delimit to get each Distributor ID into a separate cell. This may help when you are trying to populate your new tables.

 

[newmediaguy]

Thanks Katy

I am lucky because its a new system so all the current records are tests.......phew.

Although you have given me an idea for another problem i am working

Thanks

Glen

 

[jgoodman00]

Sounds good.

I would recommend a users table (there is no point recording a username & email address for each product).

You could then store CatID, ManID, UserID, DistributorID.

It might be possible to further refine this:
ProdID, (Composite of CatID, ManID, UserID)
DistributorID,

This assumes a product is unique, but can have many distributors...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[r937]

The distributor is a column that is the friendly key for another table

that's pretty funny

a list of values in a column is actually quite hostile

i liked the word james used -- "bodge"

good thing you've decided to change it

rudy

http://r937.com/

SQL Consulting

 

[ESquared]

If you need help you can always use a split function to convert the 'friendly keys' list to a rowset (could be useful for converting your design. Here's one I wrote:

IF EXISTS (SELECT * FROM sysobjects WHERE Name = N'SplitTokensInt' AND Type = 'TF') DROP FUNCTION SplitTokensInt

GO

CREATE Function SplitTokensInt(
	@InputText varchar(7999),
	@Delimiter varchar(20))
RETURNS @Array TABLE (
	TokenID int PRIMARY KEY,
	Value int)

AS

BEGIN

	DECLARE @TokenID int,
		@Pos int,
		@End int,
		@TextLength int,
		@DelimLength int

	DECLARE
		@ValueText varchar(7999)
	
	SET @TokenID = 1
	SET @InputText = LTrim(RTrim(ISNULL(@InputText,'')))
	SET @TextLength = Len(@InputText)	

	IF @TextLength = 0 RETURN
	IF NOT IsNull(@Delimiter,'') = '' BEGIN
		SET @Pos = 1
		SET @End = 1
		SET @DelimLength = Len(@Delimiter)
		SET @InputText = @InputText + @Delimiter
		SET @End = CharIndex(@Delimiter, @InputText)
		WHILE @End > 0 BEGIN
			SET @ValueText = SubString(@InputText, @Pos, @End - @Pos)
         INSERT @Array VALUES (
				@TokenID,
				CASE 
					WHEN IsNumeric(@ValueText)=1 THEN Convert(int,@ValueText)
					ELSE 0 END
			)
			SET @TokenID = @TokenID + 1
			SET @Pos = @End + @DelimLength
			SET @End = CharIndex(@Delimiter, @InputText, @Pos)
		END
	END
	
	RETURN

END

GO

SELECT * FROM dbo.SplitTokensInt('28,13,frog,51,2,0,7',',')

Note, this function can break if you give it badly formatted values, e.g., if you use a delimiter other than comma and it tries to convert ',' to a number. That's because IsNumeric thinks this is numeric but it does not cast to int very well.