Splitting the column in SQL Server

[phanikumar32]

Hello All,

I have a table SQL Server with one column.The column name is 'Name'.

This column contains names of some persons.(as shown in figure 1)

So by using those names i want to get the output as each letter as an individual column.(as shown in figure 2)


If this is possible please guide me how can i get this....

Thanks In Advance,
Phani Kumar CH.

[Luuk]

1> select substring(a,1,1) First, substring(a,2,1) Second, substring(a,3,1) Third, substring(a,4,1) Fourth, substring(a,5,1) Fifth from test;
2> go
 First Second Third Fourth Fifth
 ----- ------ ----- ------ -----
 a     b      c     d

(1 row affected)
1> select * from test;
2> go
 a
 ----
 abcd

(1 row affected)
1>

you have to expand this if you want to use more letters ;)

[phanikumar32]

Thank you Very much Luuk.

This is very Helped for me,but i want to know one more thing is that if we don't know how many characters(lett ers) are there in that column.
Means
1> select * from test;
2> go
a
----
abcd

so this we know there are 4 letters in that output.so we split in to 5 columns, as what you are sended me.
That is
1> select substring(a,1,1 ) First, substring(a,2,1 ) Second, substring(a,3,1 ) Third, substring(a,4,1 ) Fourth, substring(a,5,1 ) Fifth from test;
2> go
First Second Third Fourth Fifth
----- ------ ----- ------ -----
a b c d

So what i am asking you that if the column contains more than 5 letters (example 6 letters) i want to add the Sixth column as Dynamically and put the each letter in their respective columns.

If this is possible please guide me how can i get my required output.

Thanks In Advance,
Phani Kumar CH.

[prigupta2]

CREATE TABLE SPLITName 
( 
	Name NVARCHAR(50) 
);
GO
INSERT INTO SPLITName (Name) VALUES ('SIVA')
INSERT INTO SPLITName (Name) VALUES ('KUMAR')
INSERT INTO SPLITName (Name) VALUES ('SAI')
INSERT INTO SPLITName (Name) VALUES ('PRADEEP')
INSERT INTO SPLITName (Name) VALUES ('VIRAT')

GO

CREATE PROCEDURE spSplitName 
(
	@Name nvarchar(50)
)
AS  
BEGIN 
 SET NOCOUNT ON; 
 
Declare @NameLength		int
Declare @ColumnCount	int
Declare @AddNewColumns	int
Declare @SQLString		nvarchar(1000)
Declare @NewColumnName	int
Declare @NewName		nvarchar(50)
Declare @SubStringChar	nvarchar(1)
Declare @i				int = 1

SET @NameLength = LEN(@Name)
SET @NewName	= @Name 
																	--PRINT @NameLength

--Count total numbers of column present in Table
	SELECT @ColumnCount = Count(*) FROM INFORMATION_SCHEMA.Columns where TABLE_Name = 'SPLITName'
	
--Create New Columns if length of Name is greated then the total number of columns present in table
	IF (@ColumnCount <= @NameLength)
		BEGIN
			SET @AddNewColumns  = (@NameLength - @ColumnCount)+1 -- +1 use because table have Name column default
			SET @NewColumnName  = (@ColumnCount)
			
																	--PRINT @AddNewColumns
																	--PRINT @NewColumnName
																	--PRINT @ColumnCount
			WHILE (@AddNewColumns >= 1)
			Begin
				SET @SQLString = 'ALTER TABLE SPLITName ADD col' + convert(nvarchar(2),@NewColumnName) + ' nvarchar(50)'
				EXECUTE sp_executesql @SQLString
																	--PRINT @SQLString
				SET @AddNewColumns = @AddNewColumns - 1
				SET @NewColumnName = @NewColumnName +1
			END
		END
		
--Update Character wise charachter in columns
																	--PRINT  @NewName
	WHILE (@NameLength >= 1)
	BEGIN
		SET @SubStringChar	=	SUBSTRING(@NewName, 1, 1)
		SET @NewName		=	STUFF(@NewName,1,1,'')
																	--PRINT  @NewName
																	--PRINT @SubStringChar
		SET @SQLString = 'UPDATE SPLITName SET col'+ CONVERT(NVARCHAR(2),@i) + ' = ''' +@SubStringChar  + ''' WHERE Name = ''' + @Name +''''
		EXECUTE sp_executesql @SQLString
																	--PRINT @SQLString
		SET @i = @i+1
		SET @NameLength = @NameLength -1
																	--PRINT @i
	END
END
GO

EXECUTE spSplitName @name= 'VIRAT'
GO
 
SELECT * FROM SplitName

[Luuk]

The example from prigupta2 add columns dynamically.
But after you do

EXEC spSplitName @name='PREDEEP'

The table will have 7 columns, and doing

EXEC spSplitName @name='SIVA'

will result in the last 3 columns being empty (NULL)

Thats why, i think, its almost the same to create the table with the number of columns you need at maximum.