November 13, 2009 at 8:45 am
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
November 14, 2009 at 1:45 am
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/
November 18, 2009 at 1:02 pm
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
February 17, 2010 at 2:43 am
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
April 8, 2011 at 7:28 am
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