Archive

Archive for the ‘SQL Server’ Category

SQL two column list to two column table

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

 

ALTER
FUNCTION [dbo].[CharList_To_Table_2_Value]


(@list ntext,

@delimiter nchar(1)
= N‘,’)


RETURNS @tbl TABLE
(listpos int
IDENTITY(1, 1)
NOT
NULL,


str
varchar(4000),

                             str2 varchar(4000)

            )
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000),

             @tmpval2 nvarchar(4000),

             @num        int

 

     SET @num = 0


SET @textpos = 1


SET @leftover =


WHILE @textpos <=
datalength(@list)
/ 2


BEGIN


SET @chunklen = 4000
datalength(@leftover)
/ 2


SET @tmpstr = @leftover +
substring(@list, @textpos, @chunklen)


SET @textpos = @textpos + @chunklen

 


SET @pos =
charindex(@delimiter, @tmpstr)

 


WHILE @pos > 0


BEGIN

            if @num = 0

            begin

     SET @tmpval =
ltrim(rtrim(left(@tmpstr, @pos 1)))

                set @num = 1

            end

            else

            begin

                SET @tmpval2 =
ltrim(rtrim(left(@tmpstr, @pos 1)))

     INSERT @tbl (str, str2)
VALUES(@tmpval, @tmpval2)

                set @num = 0

            end

            
 


SET @tmpstr =
substring(@tmpstr, @pos + 1,
len(@tmpstr))


SET @pos =
charindex(@delimiter, @tmpstr)


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, str2)
VALUES
(@tmpval,
ltrim(rtrim(@leftover)))


RETURN


END

 


 

Categories: SQL Server

SQL string list to table function

From SQLServerCentral.com

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

ALTER
FUNCTION [dbo].[CharList_To_Table]


(@list ntext,

@delimiter nchar(1)
= N‘,’)


RETURNS @tbl TABLE
(listpos int
IDENTITY(1, 1)
NOT
NULL,


str
varchar(4000),

nstr nvarchar(2000))
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

 


SET @textpos = 1


SET @leftover =


WHILE @textpos <=
datalength(@list)
/ 2


BEGIN


SET @chunklen = 4000
datalength(@leftover)
/ 2


SET @tmpstr = @leftover +
substring(@list, @textpos, @chunklen)


SET @textpos = @textpos + @chunklen

 


SET @pos =
charindex(@delimiter, @tmpstr)

 


WHILE @pos > 0


BEGIN


SET @tmpval =
ltrim(rtrim(left(@tmpstr, @pos 1)))


INSERT @tbl (str, nstr)
VALUES(@tmpval, @tmpval)


SET @tmpstr =
substring(@tmpstr, @pos + 1,
len(@tmpstr))


SET @pos =
charindex(@delimiter, @tmpstr)


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, nstr)
VALUES
(ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))


RETURN


END


 

Categories: SQL Server

SQL Integer list to table function

 

Probably from SQLServerCentral.com

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

ALTER FUNCTION [dbo].[IntList_To_Table]

(@list ntext,

@delimiter nchar(1) = N’,’)

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

num int) AS

 

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

 

SET @textpos = 1

SET @leftover = ”

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 – datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

 

SET @pos = charindex(@delimiter, @tmpstr)

 

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos – 1)))

INSERT @tbl (num) VALUES(cast(@tmpval as int))

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

 

SET @leftover = @tmpstr

END

 

INSERT @tbl(num) VALUES (cast(ltrim(rtrim(@leftover)) as int))

RETURN

END


 

Categories: SQL Server

SQL Create A Comma Delimited List From a Column

From SQLServerCentral.com

Create A Comma Delimited List From a Column

Arnie Rowland, March 12, 2008

 

On occasion, there is a desire to concatenate column values from multiple rows to create a comma delimited list.

For example, from the following data:

 

MemberID     Team     FirstName

1     1     Jim

2     1     Mary

3     1     Bob

4     2     Sue

5     2     Ralph

6     2     Ellen

7     3     Bill

8     3     Linda

 

the desired output is a list of Teams and Team members, in the form of:

 

Team     Members

1     Bob, Jim, Mary

2     Ellen, Ralph, Sue

3     Bill, Linda

 

There are several methods to accomplish the desired output. First a solution for SQL Server 2005 / SQL Server 2008 , and then a solution for SQL Server 2000.

 

Create Sample Data for Both Solutions

 

— Suppress data loading messages

SET NOCOUNT ON

 

— Create Sample Data

CREATE TABLE TeamInfo

( MemberID int IDENTITY,

TeamID int,

FirstName varchar(50)

)

 

— Load Sample Data

INSERT INTO TeamInfo VALUES ( 1, ‘Jim’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Mary’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Bob’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Sue’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ralph’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ellen’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Bill’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Linda’ )

 

Return to Top

 

 

SQL Server 2005 / SQL Server 2008 Solution

 

–Retrieve desired data

SELECT

t1.TeamID,

MemberList = substring((SELECT ( ‘, ‘ + FirstName )

FROM TeamInfo t2

WHERE t1.TeamID = t2.TeamID

ORDER BY

TeamID,

FirstName

FOR XML PATH( ” )

), 3, 1000 )FROM TeamInfo t1

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Bob, Jim, Mary

2    Ellen, Ralph, Sue

3    Bill, Linda

 

Return to Top

 

 

SQL 2000 Solution

 

— SQL 2000, Retrieve desired data

— With SQL 2000, we will create a User Defined Function to do the concatenation.

— While this solution can also be used with SQL Server 2005/SQL Server 2008,

— the previous suggestion is more efficient.

 

CREATE FUNCTION dbo.fnMakeTeamList

( @TeamID int )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @TempTeam table

( Firstname varchar(20) )

DECLARE @TeamList varchar(1000)

SET @TeamList = ”

INSERT INTO @TempTeam

SELECT FirstName

FROM TeamInfo

WHERE TeamID = @TeamID

IF @@ROWCOUNT > 0

UPDATE @TempTeam

SET @TeamList = ( @TeamList + FirstName + ‘, ‘ )

RETURN substring( @TeamList, 1, ( len( @TeamList ) – 1 ))

END

 

— Usage

SELECT

TeamID,

MemberList = dbo.fnMakeTeamList( TeamId )

FROM TeamInfo

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Jim, Mary, Bob

2    Sue, Ralph, Ellen

3    Bill, Linda

 

Return to Top

 

Clean up the test environment

 

DROP FUNCTION dbo.fnMakeTeamList

DROP TABLE TeamInfo

 

 

Additional Resouces

Pass and Handle a Delimited String similar to handling an Array() of Values.

 

Return to Top

 

___________________________________________________________________________________________________________________

Page Created By: Arnie Rowland, Mar 12, 2008

Categories: SQL Server