corss apply query - 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

corss apply query

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 11:54:57

I have a table Raw as below


HostName	UserName
---------------------------------------
AIX2		actuser1
+ 		actuser2
S9	        actuser4,actuser8,actuser12


Want to get the table data re-arrange as below

HostName	UserName
-------------------------
AIX2		actuser1
+ 		actuser2
S9	        actuser4
S9              actuser8
S9              actuser12

some thing as below

INSERT INTO Raw
SELECT t.HostName, m.UserName AS Members
FROM Raw t
CROSS APPLY (
SELECT [UserName] FROM StringToTable(',',t.members) )m

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 12:06:43

I have a function StringToTable as below

CREATE FUNCTION StringToTable (@Delimiter VARCHAR(5), @String VARCHAR(max))

RETURNS @RtnValue TABLE ([ID] INT IDENTITY(1, 1),
[Value] VARCHAR(max))
AS
BEGIN
DECLARE @Cnt INT
SET @Cnt=1

WHILE (CHARINDEX(@Delimiter, @String)>0)
BEGIN
INSERT INTO @RtnValue ([Value])
SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@String, 1, CHARINDEX(@Delimiter, @String)-1)))

SET @String=SUBSTRING(@String, CHARINDEX(@Delimiter, @String)+1, LEN(@String))
SET @Cnt=@Cnt+1
END

INSERT INTO @RtnValue ([Value])
SELECT [Value] = LTRIM(RTRIM(@String))

RETURN
END

-Neil

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 12:16:10

What's your problem then?

--------------------------
Get rich or die trying
--------------------------

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 12:34:29

I am getting the results as below


AIX2	actuser1
+	actuser2
S9	actuser4,actuser8,actuser12
S9	actuser4,actuser8,actuser12
S9	actuser4,actuser8,actuser12

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 12:42:23

Got the results

After changing the query as below

SELECT t.HostName, m.UserName AS Members
FROM raw t
CROSS APPLY (
SELECT Value as UserName FROM StringToTable(',',t.UserName)
)m

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources