SQL Server Performance

comma separated cell data into rows

Discussion in 'SQL Server 2005 General Developer Questions' started by bubberz, May 13, 2008.

  1. bubberz New Member

    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. MohammedU New Member

    Use the String breaker function...check the following...
    CREATE FUNCTION dbo.udf_StringBreaker (@InputString Varchar(8000), @Delimiter Char(1) = ',' )
    RETURNS @ResultTable TABLE(String1 Varchar(255) )

    AS
    BEGIN
    WHILE CHARINDEX(@Delimiter, @InputString) <> 0
    BEGIN
    INSERT INTO @ResultTable
    SELECT SUBSTRING(@InputString, 1, CHARINDEX(@Delimiter, @InputString) - 1)
    SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString) + 1, 8000)
    END /* WHILE ... */
    INSERT INTO @ResultTable
    SELECT @InputString
    RETURN
    END
  3. bubberz New Member

    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(@SplitOn),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!
  4. Expansion New Member

    Your function is a Table-function. So it has to be in the From clause of the Select statement:
    SELECT Value FROM dbo.fSplit(ColAInputstring, ',')
    Your problem is that you want to fill two columns in your table. Maybe you can use a Join of two of your table valued functions):
    SELECT a.Value, b.Value
    FROM dbo.fSplit(ColAInputstring, ',') a
    JOIN dbo.fSplit(ColBInputstring, ',') b ON b.ID =a.ID
    Alternatively you could build the logic for separating the values in the two commaseparated strings in one function.
    (In your function in the statement
    INSERT INTO ........ SELECT Value = ltrim(rtrim....
    you can skip 'Value ='. It makes only a columnname in the SELECT, but looks like you are assigning ltrim(rtrim.... to a variable.)
    Success, Cor Westra
  5. bubberz New Member

    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

Share This Page