Disticnt data in view - 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

Disticnt data in view

Author

Topic

itpriyesh88
Starting Member

13 Posts

Posted - 2012-09-23 : 00:45:32

My Table is in this format:

ID NAME ROLE EXP
1 A X,Y,Z 7
2 B P,Q 9

in the view I want this:

ID NAME ROLE EXP
1 A X 7
1 A Y 7
1 A Z 7
2 B P 9
2 B Q 9

How to do this please Help!!!

itpriyesh88
Starting Member

13 Posts

Posted - 2012-09-23 : 02:49:52

My Table is in this format:

ID----NAME----ROLE----EXP
1----- A----- X,Y,Z---- 7
2----- B----- P,Q------ 9

in the view I want this:

ID----NAME----ROLE----EXP
1----- A------ X------ 7
1----- A------ Y------ 7
1----- A------ Z------ 7
2----- B------ P------ 9
2----- B------ Q------ 9

How to do this please Help!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-23 : 08:14:55

You need a string-splitter function to do this. A good one is Jeff Moden's here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Copy the function from Fig. 21 in that article and run it to install the function. Then, use it like in the example below:

CREATE TABLE #tmp (ID INT, [name] CHAR(1), [role] VARCHAR(32), [exp] INT);
INSERT INTO #tmp VALUES
(1,'a', 'x,y,z',7),
(2,'b','p,q',9);

SELECT
	id,
	[name],
	Item AS [Role],
	[exp]
FROM
	#tmp a
	CROSS APPLY
	(
		SELECT * FROM  dbo.DelimitedSplit8K([role],',') 
	) s

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-09-23 : 23:21:17

If you have a role table (and you should) you *might* get better performance from joining to that then working out what's valid.
Something like this (untested):

select ID, NAME, r.ROLE, EXP
from table
inner join ROLES r on
','+table.role+',' like '%,'+r.ROLE+',%'

Check out the query plans and see.

A better way would be to just not store your roles like that and fix your database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources