Columns to multiple rows - transpose based on pattern in the column

  • Hi,

    We are in the process of converting columns into rows. Columns have a delimiter from where the value needs to go into another record

    Pls find below a Sample:

    Source:

    ColA ColB

    123 999^111^123

    124 500^2564

    Expected Output:

    ColA ColB

    123 999

    123 111

    123 123

    124 500

    124 2564

    Any ideas/suggestions will be appreciated.

    Thanks in advance

  • Hi,

    Ref the valuable Jeff article, The "Numbers" or "Tally" Table: What it is and how it replaces a loop like: (One Final "Split" Trick with the Tally Table) Link:http://www.sqlservercentral.com/articles/T-SQL/62867/

  • Step1: First you can run the below split function in your server

    CREATE FUNCTION [dbo].[f_Split]

    (

    @Keyword VARCHAR(8000),

    @Delimiter VARCHAR(255)

    )

    RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000))

    AS

    BEGIN

    DECLARE @Word VARCHAR(255)

    DECLARE @TempKeyword TABLE (Keyword VARCHAR(8000))

    WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)

    BEGIN

    SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) - 1)

    SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))

    INSERT INTO @TempKeyword VALUES(@Word)

    END

    INSERT INTO @TempKeyword VALUES(@Keyword)

    INSERT @SplitKeyword

    SELECT * FROM @TempKeyword

    RETURN

    END

    Step2: Try to use this query

    --Create the #a temp table for store the values with rowId

    CREATE TABLE #a(Row INT,ColA VARCHAR(50),ColB VARCHAR(50))

    INSERT INTO #a(Row, ColA, ColB)

    SELECT Row_Number() OVER (ORDER BY ColA asc) AS Row, ColA, ColB

    FROM Source

    --

    DECLARE @Count INT

    SET @Count=(SELECT COUNT(*) FROM Source)

    DECLARE @i INT

    SET @i=1

    DECLARE @TempColA INT

    DECLARE @TempColB VARCHAR(MAX)

    --Create the table for selected output value

    CREATE TABLE #temp(ColA VARCHAR(50),ColB VARCHAR(50))

    WHILE @Count>0

    BEGIN

    SET @TempColA=(SELECT ColA FROM #a WHERE Row=@i)

    SET @TempColB=(SELECT ColB FROM #a WHERE Row=@i)

    INSERT INTO #temp(ColA,ColB)

    SELECT @TempColA,*

    FROM f_Split(@TempColB,'^')

    SET @Count=@Count-1

    SET @i=@i+1

    END

    SELECT * FROM #temp

    --Drop the two temp tables

    DROP TABLE #temp

    DROP TABLE #a

    http://www.sql-programmers.com

  • Hi.

    I had a similar problem.

    I used a function to get each word out of the string. My seperator was a space.

    I wrote a udf and then call it within a usp.

    Here is the UDF.

    Regards

    Roy

    USE [POR-MainDB]

    GO

    /****** Object: UserDefinedFunction [dbo].[udfGetAWord] Script Date: 02/17/2010 10:40:24 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:Roy Turner

    -- Create date: 09/09/08

    -- Description:Returns the Word from the string specified in the first parameter

    -- whose number is specified in the second parameter

    -- =============================================

    ALTER FUNCTION [dbo].[udfGetAWord]

    (

    @InString AS NVARCHAR(MAX),

    @WordNo AS INT

    )

    RETURNS NVARCHAR(MAX)

    AS

    BEGIN

    DECLARE @WorkString AS NVARCHAR(max)

    DECLARE @Sub AS INT

    DECLARE @Pos AS INT

    DECLARE @Outstring AS NVARCHAR(MAX)

    DECLARE @Len AS INT

    SET @WorkString = LTRIM(RTRIM(@instring))

    SET @Len = LEN(@workstring)

    SET @sub = 0

    IF @WordNo < 1 OR @len < 1

    SET @outstring = ''

    WHILE @sub < @wordno

    BEGIN

    SET @Pos = CHARINDEX(' ',@workstring)

    IF @Pos > 0

    BEGIN

    SET @sub = @Sub + 1

    SET @Outstring = LEFT(@workstring, @Pos - 1)

    SET @len = @len - @Pos

    SET @workstring = substring(@workstring, @Pos + 1, @len )

    END

    ELSE

    BEGIN

    IF @Sub = @WordNo - 1

    SET @outstring = @Workstring

    ELSE

    SET @outstring = ''

    SET @Sub = @WordNo + 1

    END

    END

    RETURN @Outstring

    END

  • To easily transpose columns into rows with its names you should use XML. In my blog I was described this with example: http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply