Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61

    Unanswered: Spliting single row into multiple rows

    I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
    My table structure is:
    Code:
    create table #scoresheet
    (Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
    Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
    Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
    I would like to have three rows with four columns.
    What I've done so far is create a stored procedure that uses a table variable:
    Code:
    create procedure sp_splitsinglerow as
    
    declare @Scoresheet_rows_table_var table (
    	ReviewArea varchar(25),
    	NumericalValue int,
    	VI varchar(10),
    	Comments nvarchar(255));
    insert  into @Scoresheet_rows_table_var
    (ReviewArea, NumericalValue, VI, Comments)
    select Decisions, DNumericalValue, DVI, DComments
    from #scoresheet
    The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.

    Any ideas on how to achieve that?

    BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The term for what you are trying to do is called "Normalizing" the data. There are many methods for an example such as the one you post. One of the most popular is the use of a UNION query:
    Code:
    select	Decisions,
    	DNumericalValue,
    	DVI,
    	DComments
    from	#scoresheet
    UNION
    select	Competence,
    	CNumericalValue,
    	CVI,
    	CComments
    from	#scoresheet
    UNION
    select	Equipment,
    	ENumericalValue,
    	EVI,
    	EComments
    from	#scoresheet
    But if you want an "automatic" method, you can try this script I wrote two years ago. It returns an SQL statement that you can execute via dynamic sql:
    Code:
    create function NormalizeXTabSQL(@TableName varchar(500), @ColumnIncrement int, @StartColumn int, @EndColumn int)
    returns varchar(4000)
    as
    begin
    --NormalizeXTab
    --blindman, 2/4/2005
    --Returns a SQL UNION statement that converts repeating
    --column groups in a table into normalized individual records.
    
    --Test parameters
    -- declare @TableName varchar(500)
    -- declare @ColumnIncrement int
    -- declare @StartColumn int
    -- declare @EndColumn int
    -- set	@TableName = 'LimsImportStaging' --Name of your target table
    -- set	@ColumnIncrement = 3  --Number of columns to return for each statement
    -- set	@StartColumn = 2
    -- set	@EndColumn = 16
    
    declare	@ColumnString varchar(500)
    declare	@ColumnCounter int
    declare	@SQLString varchar(4000)
    
    set     @ColumnCounter = isnull(@StartColumn, 1) - 1
    
    set     @EndColumn = isnull(@EndColumn,
            (select	count(*)
            from	sysobjects
                    inner join syscolumns on sysobjects.id = syscolumns.id
            where	sysobjects.name = @TableName))
    
    while   @ColumnCounter < @EndColumn
    begin
            set	@ColumnString = null
            set	@ColumnCounter = @ColumnCounter + @ColumnIncrement
    
            select	@ColumnString = isnull(@ColumnString + ', ', '') + syscolumns.name
            from	sysobjects
                    inner join syscolumns on sysobjects.id = syscolumns.id
            where	sysobjects.name = @TableName
                    and colid > @ColumnCounter - @ColumnIncrement
                    and colid <= @ColumnCounter
                    and colid <= @EndColumn
            order by colid
    	
            set     @SQLString = isnull(@SQLString + char(13) + 'UNION' + char(13), '') + 'select ' + @ColumnString  + ' from ' + @TableName
    end
    
    return	@SQLString
    
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    Excellent Blindman, thanks very much!

Posting Permissions

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