Help me to create a view

Question

Post reply

Help me to create a view

Jay_dba

Grasshopper

Points: 17
More actions
October 25, 2009 at 5:10 am

#103844

I have a table
CREATE TABLE #IMaster (ID INT, ITEM1 VARCHAR(100))
GO
INSERT #IMaster
SELECT 1,'pencil'
UNION
SELECT 2,'pen/pencil'
UNION
SELECT 3,'ink'
UNION
SELECT 4,'pen/pencil/ink/'
I need to create a view like this
IDid1 ITEM1
11pencil
21pen
22pencil
31ink
41pen
42pencil
43ink
We are still working on sql 2000.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 1

check SSC for a split (table valued) function .

e.g.

/*

* Split a delimited string using a table valued function with XML

*/

-- table-valued function

CREATE FUNCTION dbo.fn_DBA_Split2(@Data varchar(4000),

@Delimiter varchar(10) = ',')

RETURNS @tblSplit TABLE

(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

Item varchar(4000) NULL)

AS

BEGIN

-- select e.*

-- from pubs..employee E

-- inner join master.dbo.fn_DBA_Split2('ikke,jij,hij,zij,Karin',',') U

-- on E.fname = U.item

DECLARE @x XML

SET @x = '<i>' + REPLACE( @data, @Delimiter, '</i><i>') + '</i>'

INSERT INTO @tblSplit (Item)

SELECT x.i.value('.', 'VARCHAR(4000)')

FROM @x.nodes('//i') x(i)

RETURN

END

go

select e.*

from pubs..employee E

inner join master.dbo.fn_DBA_Split2('ikke,jij,hij,zij,Karin',',') U

on E.fname = U.item

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Oh... be careful, Johan... XML splits are relatively slow compared to many other methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Jay_dba (10/25/2009)
I have a table
CREATE TABLE #IMaster (ID INT, ITEM1 VARCHAR(100))
GO
INSERT #IMaster
SELECT 1,'pencil'
UNION
SELECT 2,'pen/pencil'
UNION
SELECT 3,'ink'
UNION
SELECT 4,'pen/pencil/ink/'
I need to create a view like this
IDid1 ITEM1
11pencil
21pen
22pencil
31ink
41pen
42pencil
43ink
We are still working on sql 2000.
Thanks in advance

Take a look at the following article, Jay.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jay_dba Grasshopper Points: 17 More actions · Answer 4

Thank you ALZDBA, Jeff .

ALZDBA,

I am getting an error on @x.nodes('/i') x(i) . can you please check.

Jeff,

Thanks for the article. its too good.

But my problem still exists

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 5

Jeff Moden (10/25/2009)
Oh... be careful, Johan... XML splits are relatively slow compared to many other methods.

Indeed, I copy/pasted the wrong example code out of one of my test scripts.

That's why I hinted to search ssc for split functions.

OP would have come to your marvellous article without a doubt 😉

Should have pointed to that in the first place 😎

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden (10/25/2009)
Oh... be careful, Johan... XML splits are relatively slow compared to many other methods.

Plus the OP indicated he was still on SQL 2000 (despite posting in the SQL 2005 forums). So XML is out of the picture anyway.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 7

Jay_dba (10/26/2009)
Thank you ALZDBA, Jeff .
ALZDBA,
I am getting an error on @x.nodes('/i') x(i) . can you please check.
Jeff,
Thanks for the article. its too good.
But my problem still exists

Aaarchh ... sql2000 ..... doesn't have XML datatype.

My bad. Sorry for that.

You'll get there using Jeffs article !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me