SQL SERVER – Function to Convert List to Table

Update : (5/5/2007)
I have updated the UDF to support SQL SERVER 2005.
Visit :SQL SERVER – UDF – Function to Convert List to Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Function to Convert List to Table

  1. I am new sqlserver i tried every query suggest by you but i cant remove my duplicate row likes Date,Day

    1 2 12-Apr-2008 9:00 AM 11:00 PM Saturday 14 4/12/2008 3:17:11 AM 04 Saturday 2008
    2 2 12-Apr-2008 7:00 AM 11:00 PM Saturday 16 4/12/2008 3:19:27 AM 04 Saturday 2008
    3 2 11-Apr-2008 9:00 AM 10:00 PM Friday 13 4/11/2008 3:24:32 AM 04 Friday 2008
    7 2 11-Apr-2008 8:00 AM 10:00 PM Friday 14 4/11/2008 3:28:18 AM 04 Friday 2008
    8 2 11-Apr-2008 5:00 AM 10:00 PM Friday 17 4/11/2008 3:29:20 AM 04 Friday 2008
    9 2 11-Apr-2008 10:30AM 11:30PM FriDay 12 fbggfb 4/12/2008 3:17:11 AM dfbdfbdfb 04 FriDay 2008

  2. i have some problem ,

    i want to create a table name country (country list) and want to insert country name (500 country name )
    it is to difficult for me apply
    this “insert into country (country_name) (‘ INDIA’)”
    single name again an again

    so i want to know how will i insert large amount of data in single entity as in country _name

    With Regard
    Anurag Kulshrestha

  3. hello anurag kulshrestha,Solution for U

    set nocount on;
    declare @values nvarchar(4000);
    set @values=’India|Pakistan|Iran|USA|Australia|Greek|German|turkey|New Zealand|China|Brazil’
    declare @Del char(1);
    set @Del=’|’;
    set @values=@values+@Del;
    declare @value nvarchar(100);
    WHILE charindex(@Del,@values,0) 0
    BEGIN
    select @value=rtrim(ltrim(substring(@values,1,charindex(@Del,@values,0)-1))),@values=rtrim (ltrim(substring(@values,charindex(@Del,@values,0)+1,len(@values))))
    if not exists(select @value from daybook where Title=@value)
    begin
    Insert into DayBook(Title) values(@value)
    end
    END

  4. Hi ,pinal

    I just want to know the query which we can use to list all available servers in mssql 2000/2005.

    Regards..
    Ganesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s