Transpose Single column into Multiple column

Question

Post reply

Transpose Single column into Multiple column

karthik M

One Orange Chip

Points: 29139
More actions
April 20, 2010 at 2:29 am

#219316

All,
I have a requirement.
create table #temp
(name varchar(50)
)
insert into #temp values ('karthik~gokul~siva~magesh~')
insert into #temp values ('kar~go~sa~mag~')
insert into #temp values ('k~g~s~m~')
Expected output:
c1 c2 c3 c4
karthik gokul siva magesh
kar go sa mag
k g s m

Inputs are welcome!
karthik

Viewing 9 posts - 1 through 8 (of 8 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

See:

http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Dohsan SSCrazy Points: 2972 More actions · Answer 2

You will have to adjust the pivot for any additional parts to the initial string

DECLARE @test-2 TABLE

(

RowID INT IDENTITY(1,1),

DelimitString VARCHAR(50)

PRIMARY KEY CLUSTERED (RowID)

)

INSERT INTO @test-2 (DelimitString)

SELECT 'karthik~gokul~siva~magesh~' UNION ALL

SELECT 'kar~go~sa~mag~' UNION ALL

SELECT 'k~g~s~m~'

;WITH Split (RowID, StringPos, Item)

AS

(

SELECT Test.RowID, A.StringPos, A.Item FROM @test-2 AS Test

CROSS APPLY

(

SELECT

ROW_NUMBER() OVER (ORDER BY Test.RowID ASC) AS StringPos,

SUBSTRING(Test.DelimitString, TA.N, CHARINDEX('~', Test.DelimitString, TA.N) - TA.N) AS Item

FROM Tally AS TA

WHERE TA.N < LEN(Test.DelimitString) + 1

AND SUBSTRING('~' + Test.DelimitString , TA.N, 1) = '~'

) AS A

)

SELECT

RowID,

C1 = [1],

C2 = [2],

C3 = [3],

C4 = [4]

FROM SPLIT SP

PIVOT (MAX(Item) FOR StringPos IN ([1],[2],[3],[4])) AS PivotAlias

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You could also create the split function separately, as it comes handy very often:

CREATE FUNCTION [dbo].[fSplit]

(

@Parameter VARCHAR(8000),

@SplitOn char(1)

)

RETURNS @Elements TABLE

(

ID INT identity(1,1),

Value VARCHAR(8000)

)

AS

BEGIN

--===== Add start and end commas to the Parameter so we can handle

-- single elements

SET @Parameter = @splitOn + @Parameter + @SplitOn

--===== Join the Tally table to the string at the character level and

-- when we find a comma, insert what's between that command and

-- the next comma into the Elements table

INSERT INTO @Elements (Value)

SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(@splitOn,@Parameter,N+1)-N-1)

FROM dbo.Tally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = @splitOn --Notice how we find the comma

RETURN

END

CREATE TABLE #temp(

name VARCHAR(50)

)

INSERT INTO #temp VALUES ('karthik~gokul~siva~magesh~')

INSERT INTO #temp VALUES ('kar~go~sa~mag~')

INSERT INTO #temp VALUES ('k~g~s~m~')

SELECT *

FROM #temp AS T

CROSS APPLY (

SELECT [1],[2],[3],[4]

FROM (

SELECT id, value

FROM #temp AS a

CROSS APPLY dbo.FSplit(name, '~') AS b

WHERE value <> ''

AND A.name = T.name

) AS data

PIVOT (MIN(value) FOR id IN ([1],[2],[3],[4])) as pvt

) AS P

-- Gianluca Sartori

Paul White SSC Guru Points: 150468 More actions · Answer 4

Function:

CREATE ASSEMBLY Utility

AUTHORIZATION dbo

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009478CD4B0000000000000000E00002210B0108000010000000200000000000007E2A00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300408500001000001000000000100000100000000000001000000000000000000000002C2A00004F000000004000002803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000840A0000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000602A00000000000048000000020005003C220000F00700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F0F00000A2D1A036F0F00000A2D12026F1000000A036F1000000A73040000062A168D13000001168D1300000173040000062A4A03027415000001731200000A81040000012A1E02281300000A2A033003004C0000000000000002281300000A02037D0300000402038E697D0500000402047D0400000402048E69D27D0600000402027B0600000417FE017D0700000402167D0100000402027B06000004155A7D020000042A7E027B03000004027B01000004027B02000004027B0100000459731400000A2A133003000101000001000011027B02000004027B050000043202162A02027B02000004027B06000004587D01000004027B010000040A3893000000170B027B070000042C16027B030000040693027B0400000416932E39160B2B35160C2B28060858027B050000042F14027B0300000406085893027B0400000408932E04160B2B0E081758D20C08027B0600000432CF072C3702067D02000004027B02000004027B0100000459163102172A06027B060000041759580A02257B01000004027B06000004587D010000040617580A06027B050000043F61FFFFFF02027B02000004027B06000004587D0100000402027B050000047D02000004027B02000004027B0100000459163102172A162A5A02167D0100000402027B06000004155A7D020000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000DC020000237E0000480300006803000023537472696E677300000000B00600000800000023555300B8060000100000002347554944000000C80600002801000023426C6F6200000000000000020000015717A2010902000000FA01330016000001000000150000000300000007000000070000000600000001000000140000000D0000000100000001000000010000000100000001000000020000000100000000000A0001000000000006004C0045000600660053000A0093007E000A00AE007E0006004E013C0106008601670106009A013C010600B3013C010600CE013C010600E9013C01060002023C0106001B023C0106003A023C01060057023C0106008E026E020600AE026E020A00EF02D4020A000A03D40206003B03450006004903670106005603450000000000010000000000010001000100100016000000050001000100020010002B0000000500010004000100E7002F000100EF002F002100F700320021000101320021000B012F0021001501360021002201390050200000000096009C000A0001008520000000009600B800130003009820000000008618C6001B000500A020000000008618C6001F000500F82000000000E609CC0027000700182100000000E601D8002B000700252200000000E601E1001B000700000001000403000002001C03000001004003020002004403000001005D03000002001C03030009002900C60040003100C60045003900C60040004100C60040004900C60040005100C60040005900C60040006100C60040006900C60040007100C60040007900C6004A008100C6001B008900C6001B009100C6001B00190026032B0019003103C200A100C6001B002100C60040000900C6001B00A900C600C70020006B004F00240073009C002E003300F3002E001300D5002E002300D5002E002B00DB002E004B00D5002E00630009012E004300D5002E003B00D5002E005300F3002E005B00000144007300AF00CF0003000100000034013C000200050003000480000001000000B20E72A0000000000000CC02000002000000000000000000000001003C000000000002000000000000000000000001007200000000000300020000000000003C4D6F64756C653E005574696C6974792E646C6C0055736572446566696E656446756E6374696F6E730053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C537472696E670046696C6C526F775F4D756C7469002E63746F72006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E740053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005574696C697479004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500496E7075740053716C46616365744174747269627574650044656C696D69746572006765745F49734E756C6C006765745F56616C75650043686172006F626A006974656D004F757441747472696275746500537472696E6700546865537472696E67000000032000000000006FBED5A13E7BD44CAB76AF294B9FD36F0008B77A5C561934E0890800021209120D120D070002011C10111103200001072002011D031D030320001C0320000202060803061D030206050206020328001C042001010E042001010204200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E7661726368617228343030302912010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF0000000420001D03072003011D03080805070308020505010000000017010012436F7079726967687420C2A920203230313000000C0100075574696C69747900000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301542A000000000000000000006E2A0000002000000000000000000000000000000000000000000000602A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010072A0B20E0000010072A0B20E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C0200000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C00690074007900000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700360032002E00340031003000370034000000000038000C00010049006E007400650072006E0061006C004E0061006D00650000005500740069006C006900740079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005500740069006C006900740079002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C00690074007900000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700360032002E00340031003000370034000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700360032002E003400310030003700340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000803A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION dbo.SplitString_Multi

(

@Input NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE (item NVARCHAR(4000) NULL)

WITH EXECUTE AS CALLER

AS EXTERNAL NAME Utility.UserDefinedFunctions.SplitString_Multi

Test:

DECLARE @test-2 TABLE

(

RowID INTEGER IDENTITY(1,1) NOT NULL

PRIMARY KEY CLUSTERED,

DelimitString VARCHAR(50) NOT NULL

);

INSERT @test-2 (DelimitString)

SELECT 'karthik~gokul~siva~magesh~' UNION ALL

SELECT 'kar~go~sa~mag~' UNION ALL

SELECT 'k~g~s~m~';

SELECT Split.RowID,

C1 = MAX(CASE WHEN Split.rn = 1 THEN Split.item ELSE '' END),

C2 = MAX(CASE WHEN Split.rn = 2 THEN Split.item ELSE '' END),

C3 = MAX(CASE WHEN Split.rn = 3 THEN Split.item ELSE '' END),

C4 = MAX(CASE WHEN Split.rn = 4 THEN Split.item ELSE '' END)

FROM (

SELECT T.RowID,

iTVF.item,

rn = ROW_NUMBER() OVER (PARTITION BY RowID ORDER BY (SELECT 0))

FROM @test-2 T

CROSS

APPLY dbo.SplitString_Multi (T.DelimitString, N'~') iTVF

) Split

GROUP BY Split.RowID

ORDER BY Split.RowID;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

karthik M One Orange Chip Points: 29139 More actions · Answer 5

I thought to use Tally table to split the string...but i got struck up when i think about transposing a column into multiple columns. Because the Tally table transpose the delimited values into a row.

Actually PIVOT concept didn't hit on top of my head.

Thanks a lot for all folks.

I just simplified the query as below.

DECLARE @test-2 TABLE

(

RowID INT IDENTITY(1,1),

DelimitString VARCHAR(50)

PRIMARY KEY CLUSTERED (RowID)

)

INSERT INTO @test-2 (DelimitString)

SELECT 'karthik~gokul~siva~magesh~' UNION ALL

SELECT 'kar~go~sa~mag~' UNION ALL

SELECT 'k~g~s~m~'

SELECT

RowID,

C1 = [1],

C2 = [2],

C3 = [3],

C4 = [4]

FROM (

SELECT RowID,

ROW_NUMBER() OVER (PARTITION BY ROWID ORDER BY RowID ASC) AS StringPos,

SUBSTRING(DelimitString, TA.N, CHARINDEX('~', DelimitString, TA.N)- N) AS Item

FROM Tally TA, @test-2

WHERE TA.N < LEN(DelimitString) + 1

AND SUBSTRING('~' + DelimitString , TA.N, 1) = '~'

)SP

PIVOT (MAX(Item) FOR StringPos IN ([1],[2],[3],[4])) AS PivotAlias

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 6

i think i need to study how PIVOT is working internally...because the concept is unclear for me...

if anybody explain it little bit more,it would help me....

PIVOT (MAX(Item) FOR StringPos IN ([1],[2],[3],[4])) AS PivotAlias

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

karthikeyan-444867 (4/20/2010)
i think i need to study how PIVOT is working internally...because the concept is unclear for me...
if anybody explain it little bit more,it would help me....
PIVOT (MAX(Item) FOR StringPos IN ([1],[2],[3],[4])) AS PivotAlias

There are some excellent descriptions here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

spaghettidba

SSC Guru

Points: 105732

April 20, 2010 at 6:59 am

#1154526

Using Pivot and Unpivot

-- Gianluca Sartori

Transpose Single column into Multiple column

Cookies on SQLServerCentral