Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    8

    Question Unanswered: Data row comma separated cell to many rows

    Hello!

    We are on SqlServer 2005.

    Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.

    I have some data that is given to me that has two columns (below is for an example):
    Column A is an identifiying number, i.e. for a project
    Column B is a comma separated list of account strings for the project

    A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):

    AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
    BB.ProjectBuildFence ----- X900, 6789, 9000, 9876

    What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
    AA.ProjectBuildTower ----- 2222
    AA.ProjectBuildTower ----- 3333
    AA.ProjectBuildTower ----- 4444
    AA.ProjectBuildTower ----- 5555

    BB.ProjectBuildFence ----- X900
    BB.ProjectBuildFence ----- 6789
    BB.ProjectBuildFence ----- 9000
    BB.ProjectBuildFence ----- 9876

    Any suggestions would greatly help!

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, what Pootle said.

    Click here for a SQL 2000 version of a split function.

    -PatP

  4. #4
    Join Date
    Jul 2005
    Posts
    8
    I'm trying to use this example:

    --*****************
    --*****************

    USE [ReportingDB]
    GO
    /****** Object: UserDefinedFunction [dbo].[fSplit] Script Date: 05/13/2008 12:03:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[fSplit]
    (
    @List VARCHAR(6000),
    @SplitOn VARCHAR(5)
    )
    RETURNS @RtnValue TABLE
    (

    ID INT identity(1,1),
    Value VARCHAR(100)
    )
    AS
    BEGIN
    WHILE (Charindex(@SplitOn,@List)>0)
    BEGIN
    INSERT INTO
    @RtnValue (value)
    SELECT
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@ List)-1)))

    SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@Spl itOn),len(@List))
    END

    INSERT INTO
    @RtnValue (Value)
    SELECT
    Value = ltrim(rtrim(@List))

    RETURN
    END

    --***************
    --***************

    I've added it to my UDFs under that database under table valued Functions, but when I run the following SQL:
    SELECT colA, dbo.fSplit([Program Code], ',') AS Expr1
    FROM mainTable

    ...I get:
    Can't find column dbo, or user defined funciton or aggregate fSplit, or the name is ambiguous.

    I'm sure it's something goofy I'm doing!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It would be something like:
    Code:
    SELECT *
    FROM dbo.fSplit('weve, got, to, get, a, bigger, boat')
    The function returns a table it is NOT a scalar function.

  6. #6
    Join Date
    Jul 2005
    Posts
    8
    All, this did the trick ...and thanks for you help!

    Declare @Temp Table(ColumnA VarChar(40), ColumnB VarChar(400))

    insert into @Temp Values('AA.ProjectBuildTower','2222, 3333, 4444, 5555')
    insert into @Temp Values('BB.ProjectBuildFence','X900, 6789, 9000, 9876')

    Declare @Output Table(Header VarChar(40), Data VarChar(20))

    While Exists(Select * From @Temp Where CharIndex(',', ColumnB) > 0)
    Begin
    insert into @output(Header, Data)
    Select ColumnA, Left(ColumnB, CharIndex(',', ColumnB)-1)
    From @Temp
    Where CharIndex(',', ColumnB) > 0

    Update @Temp
    Set ColumnB = LTrim(Right(ColumnB, Len(ColumnB)-CharIndex(',', ColumnB)))
    End

    Insert Into @Output(Header, Data)
    Select ColumnA, ColumnB
    From @Temp

    Select * From @Output Order By Header, Data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •