Data - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Data

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 05:01:27

I have the below data in a table within SQL:

ID System KEY Value
1 miv3demo Discoverer_CoreOptions Selection,Cube,Chart

What I would like to do is write a query to transform it so that it looks like:

ID System KEY Value
1 miv3demo Discoverer_CoreOptions Selection,Cube,Chart
1 miv3demo Discoverer_CoreOptions Cube
1 miv3demo Discoverer_CoreOptions Chart

Is there a way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:09:12

[code]SELECT t.ID,t.System,t.KEY,f.Val
FROM YoutTable t
CROSS APPLY dbo.ParseValues(t.Value)f[/code]

parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111602

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 05:29:30

I have tried to create the parsevalues function but got the following error?

Msg 102, Level 15, State 1, Procedure ParseValues, Line 7
Incorrect syntax near 'varchar'.

CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val int varchar(1000) )
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:33:21

quote:
Originally posted by starnold

I have tried to create the parsevalues function but got the following error?

Msg 102, Level 15, State 1, Procedure ParseValues, Line 7
Incorrect syntax near 'varchar'.

CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val ~~int~~ varchar(1000) )
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

you dont need int there. thats was striked out and edited to varchar

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 05:43:40

Cool that has run ok but getting an error when running the query? Key is definitely a column in the table

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'KEY'.

SELECT T.ID,T.SYSTEMNAME,T.KEY,F.VALUE
FROM USERCONFIG T
CROSS APPLY dbo.ParseValues(t.Value)f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:52:00

quote:
Originally posted by starnold

Cool that has run ok but getting an error when running the query? Key is definitely a column in the table

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'KEY'.

SELECT T.[ID],T.[SYSTEMNAME],T.[KEY],F.[Val]
FROM USERCONFIG T
CROSS APPLY dbo.ParseValues(t.[Value])f

that may be becuse key is a reserved word. wrap columns in braces [] as above and try

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 05:53:19

grrr still getting an error lol sorry about this

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:59:32

show your used code?

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 06:02:23

SELECT T.ID,T.SYSTEMNAME,T.[KEY],F.VALUE
FROM USERCONFIG T
CROSS APPLY dbo.ParseValues(t.Value)f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:08:15

quote:
Originally posted by starnold

SELECT T.ID,T.SYSTEMNAME,T.[KEY],F.VALUE
FROM USERCONFIG T
CROSS APPLY dbo.ParseValues(t.Value)f

SELECT	T.ID,T.SYSTEMNAME,T.[KEY],F.VAL
FROM	USERCONFIG T
CROSS APPLY dbo.ParseValues(T.VALUE,',')F

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 06:11:52

still got the same error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:19:18

does this both work fine?
select * from dbo.ParseValues('1,2,3,4',',')t

select * from ParseValues('1,2,3,4',',')t

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 06:24:19

yes both of those work fine :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:37:05

quote:
Originally posted by starnold

yes both of those work fine :D

put [] for all

SELECT	T.[ID],T.[SYSTEMNAME],T.[KEY],F.[VAL]
FROM	USERCONFIG T
CROSS APPLY dbo.ParseValues(T.[VALUE],',')F

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 06:42:26

this is very frustrating! it gave the same error

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-09 : 06:47:42

are you really using SQL 2005? or 2000?

Em

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 06:50:23

2005 :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:59:31

Are you sure you're having only the single statement alone or are you run this as part of some other batch?

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-09 : 07:04:53

I am only running this statement:

SELECT T.[ID],T.[SYSTEMNAME],T.[KEY],F.[VAL]
FROM USERCONFIG T
CROSS APPLY dbo.ParseValues(T.[VALUE],',')F

Against this table of data:

1 miv3demo Discoverer_CoreOptions Selection,Cube,Chart,Tree,Venn,DataGrid,Report,Expression,ServerCacheAdmin,ResetVariableList G
1 miv3demo Discoverer_PlugIns DecisionTree,Map,Linkage,ModelReport,Profile,VirtualVariableFrequency,VirtualVariableRecency,VirtualVariableValue,TransactionAnalysis,BasketAnalysis,SimpleModelling,VirtualVariablePWE,Licensing,DecisionTreeModelOutputWizard,PreviousOrder,UrnTransformations,VirtualVariableTerritories,VirtualVariableDriveTime,VirtualVariableDriveZone,CreateVirtualVariable,UpdateVirtualVariable,DateBander,VirtualVariableNumericBander,VirtualVariableExpression,VirtualVariableFromFile,VirtualVariableSummary,CreateAndUpdateVirtualVariable,VirtualVariableManager,Cascade G
1 miv3demo Excelsior_Options Excelsior_Developer,Excelsior_Viewer G
1 miv3demo hidden_folders Hidden,Hitachi G
1 miv3demo hidden_folders NULL U
2 miv3demo Discoverer_CoreOptions Selection,Cube,Chart,Tree,Venn,DataGrid,Report,DecisionTree,Linkage,Map,ModelReport,Profile,Expression,DecisionTree,Linkage,Map,ModelReport,Profile,ResetVariableList G
2 miv3demo Discoverer_PlugIns DecisionTree,DecisionTreeModelOutputWizard,Linkage,Map,ModelReport,Profile,SimpleModelling,VirtualVariableTerritories,UrnTransformations,VirtualVariable,UpdateVirtualVariable,VirtualVariablePWE G
2 miv3demo hidden_folders Hidden,Customer,Reed G
2 miv3demo hidden_folders U
3 miv3demo Discoverer_CoreOptions Selection,Cube,Chart,Tree,Venn,DataGrid,Report,DecisionTree,Linkage,Map,ModelReport,Profile,Expression,DecisionTree,Linkage,Map,ModelReport,Profile,ResetVariableList G
3 miv3demo Discoverer_PlugIns DecisionTree,Linkage,Map,ModelReport,Profile,SimpleModelling,VirtualVariableTerritories,UrnTransformations,VirtualVariable,UpdateVirtualVariable,VirtualVariablePWE G
3 miv3demo hidden_folders Hidden,Customer,Azzuri,Reed G
5 miv3demo hidden_folders LeaseP,Hidden G
62 miv3demo Discoverer_PlugIns DecisionTree,DecisionTreeModelOutputWizard,Linkage,Map,ModelReport,Profile,SimpleModelling,VirtualVariableTerritories,UrnTransformations,VirtualVariable,UpdateVirtualVariable,VirtualVariablePWE,Licensing U
62 miv3demo Excelsior_Options Excelsior_Viewer U
NULL NULL NULL NULL NULL

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-09 : 08:49:58

ok, 2005.... compatability setting?

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources