Row into column - 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

Row into column

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-21 : 01:39:11

Alter FUNCTION dbo.Find$
(
@Delimiter CHAR,
@Text varchar(max)
)
RETURNS @Result TABLE ( RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY,Data VARCHAR(8000))
AS
BEGIN
DECLARE @NextPos INT,
@LastPos INT
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0
WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END
IF @NextPos <= @LastPos
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)
RETURN
END
go

declare @a varchar(max)
set @a=REPLACE(('"2852$tobramycin 40 mg\/mL injectable solution$1$mg\/kg$2409"'),'"','')
select * from dbo.Find$('$',@a)

--Output i am getting row wise
Data
2852
tobramycin 40 mg\/mL injectable solution
1
mg\/kg
2409
---But i need output in col wise

Raghu' S

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-21 : 02:08:49

And how your output should look like ???

--------------------------
http://connectsql.blogspot.com/

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-21 : 02:21:48

After each $ i need separate column for each record
like
2852 'tobramycin 40 mg\/mL injectable solution' 1 'mg\/kg' 2409
In the above output without single quot

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources