uncode Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

uncode Column

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2014-04-02 : 03:41:30

Declare @Code nvarchar = 'gfht562'
I need convert it to
col1 col2 col3 col4 ............................ etc
g f h t ............................ etc

Robowski
Posting Yak Master

101 Posts

Posted - 2014-04-02 : 08:14:08

Need more information than just 'etc'

Will the code you want to split into columns always be the same length for example? or will it vary? will there be multiple values you want to split into the same table or multiple tables and will they always be the same length?

quote:
Originally posted by samir.first

Declare @Code nvarchar = 'gfht562'
I need convert it to
col1 col2 col3 col4 ............................ etc
g f h t ............................ etc

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-02 : 08:24:28

Columns are generated dynamically .

CREATE FUNCTION dbo.fnSplitStringListXML (
@StringList VARCHAR(MAX),
@Delimiter CHAR(1))

RETURNS @TableList TABLE(ID int,ColumnValue VARCHAR(128))
BEGIN

IF @StringList = '' RETURN
IF @Delimiter = ''
BEGIN
WITH Split AS
( SELECT CharOne=LEFT(@StringList,1),R=RIGHT(@StringList,len(@StringList)-1)
UNION ALL
SELECT LEFT(R,1), R=RIGHT(R,len(R)-1)
FROM Split
WHERE LEN(R)>0 )
INSERT @TableList
SELECT 1,CharOne FROM Split
OPTION ( MAXRECURSION 0)
RETURN
END -- IF
DECLARE @XML xml
SET @XML = '<root><csv>'+replace(@StringList,@Delimiter,'</csv><csv>')+
'</csv></root>'
INSERT @TableList
SELECT 1,rtrim(ltrim(replace(Word.value('.','nvarchar(128)'),char(10),'')))
AS ListMember
FROM @XML.nodes('/root/csv') AS WordList(Word)
RETURN
END -- FUNCTION
GO

SELECT * INTO #temp FROM dbo.fnSplitStringListXML ('gfht562','')

DECLARE @SQL NVARCHAR(MAX),
@i INT,
@MaxCount INT

SELECT @MaxCount = MAX(cnt)
FROM (
SELECT ID,
COUNT(ColumnValue) AS cnt
FROM #temp
GROUP BY Id
) X;

SET @i = 0;

WHILE @i < @MaxCount
BEGIN
SET @i = @i + 1;
SET @SQL = COALESCE(@Sql + ', ', '') + 'Col' + cast(@i AS NVARCHAR(10));
END

SET @SQL = N';WITH CTE AS (
SELECT Id, ColumnValue, ''Col''
+ CAST(row_number() OVER (PARTITION BY ID ORDER BY Id DESC) AS Varchar(10)) AS RowNo
FROM #temp)
SELECT *
FROM CTE
PIVOT (MAX(ColumnValue) FOR RowNo IN (' + @SQL + N')) pvt';

EXECUTE (@SQL);

DROP TABLE #Temp

Result :

Id Col1 Col2 Col3 Col4 Col5 Col6 Col7
1 g f h t 5 6 2

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources