By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,944 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,944 IT Pros & Developers. It's quick & easy.

Splitting the column in SQL Server

P: 22
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.
Attached Images
File Type: jpg 1.jpg (10.7 KB, 109 views)
File Type: jpg 2.jpg (29.8 KB, 119 views)
May 21 '14 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,043
Expand|Select|Wrap|Line Numbers
  1. 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. 2> go
  3.  First Second Third Fourth Fifth
  4.  ----- ------ ----- ------ -----
  5.  a     b      c     d
  6.  
  7. (1 row affected)
  8. 1> select * from test;
  9. 2> go
  10.  a
  11.  ----
  12.  abcd
  13.  
  14. (1 row affected)
  15. 1>
  16.  
you have to expand this if you want to use more letters ;)
May 21 '14 #2

P: 22
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(letters) 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.
May 22 '14 #3

P: 33
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE SPLITName 
  2.     Name NVARCHAR(50) 
  3. );
  4. GO
  5. INSERT INTO SPLITName (Name) VALUES ('SIVA')
  6. INSERT INTO SPLITName (Name) VALUES ('KUMAR')
  7. INSERT INTO SPLITName (Name) VALUES ('SAI')
  8. INSERT INTO SPLITName (Name) VALUES ('PRADEEP')
  9. INSERT INTO SPLITName (Name) VALUES ('VIRAT')
  10.  
  11. GO
  12.  
  13. CREATE PROCEDURE spSplitName 
  14. (
  15.     @Name nvarchar(50)
  16. )
  17. AS  
  18. BEGIN 
  19.  SET NOCOUNT ON; 
  20.  
  21. Declare @NameLength        int
  22. Declare @ColumnCount    int
  23. Declare @AddNewColumns    int
  24. Declare @SQLString        nvarchar(1000)
  25. Declare @NewColumnName    int
  26. Declare @NewName        nvarchar(50)
  27. Declare @SubStringChar    nvarchar(1)
  28. Declare @i                int = 1
  29.  
  30. SET @NameLength = LEN(@Name)
  31. SET @NewName    = @Name 
  32.                                                                     --PRINT @NameLength
  33.  
  34. --Count total numbers of column present in Table
  35.     SELECT @ColumnCount = Count(*) FROM INFORMATION_SCHEMA.Columns where TABLE_Name = 'SPLITName'
  36.  
  37. --Create New Columns if length of Name is greated then the total number of columns present in table
  38.     IF (@ColumnCount <= @NameLength)
  39.         BEGIN
  40.             SET @AddNewColumns  = (@NameLength - @ColumnCount)+1 -- +1 use because table have Name column default
  41.             SET @NewColumnName  = (@ColumnCount)
  42.  
  43.                                                                     --PRINT @AddNewColumns
  44.                                                                     --PRINT @NewColumnName
  45.                                                                     --PRINT @ColumnCount
  46.             WHILE (@AddNewColumns >= 1)
  47.             Begin
  48.                 SET @SQLString = 'ALTER TABLE SPLITName ADD col' + convert(nvarchar(2),@NewColumnName) + ' nvarchar(50)'
  49.                 EXECUTE sp_executesql @SQLString
  50.                                                                     --PRINT @SQLString
  51.                 SET @AddNewColumns = @AddNewColumns - 1
  52.                 SET @NewColumnName = @NewColumnName +1
  53.             END
  54.         END
  55.  
  56. --Update Character wise charachter in columns
  57.                                                                     --PRINT  @NewName
  58.     WHILE (@NameLength >= 1)
  59.     BEGIN
  60.         SET @SubStringChar    =    SUBSTRING(@NewName, 1, 1)
  61.         SET @NewName        =    STUFF(@NewName,1,1,'')
  62.                                                                     --PRINT  @NewName
  63.                                                                     --PRINT @SubStringChar
  64.         SET @SQLString = 'UPDATE SPLITName SET col'+ CONVERT(NVARCHAR(2),@i) + ' = ''' +@SubStringChar  + ''' WHERE Name = ''' + @Name +''''
  65.         EXECUTE sp_executesql @SQLString
  66.                                                                     --PRINT @SQLString
  67.         SET @i = @i+1
  68.         SET @NameLength = @NameLength -1
  69.                                                                     --PRINT @i
  70.     END
  71. END
  72. GO
  73.  
  74. EXECUTE spSplitName @name= 'VIRAT'
  75. GO
  76.  
  77. SELECT * FROM SplitName
May 22 '14 #4

Expert 100+
P: 1,043
The example from prigupta2 add columns dynamically.
But after you do
Expand|Select|Wrap|Line Numbers
  1. EXEC spSplitName @name='PREDEEP'
The table will have 7 columns, and doing
Expand|Select|Wrap|Line Numbers
  1. 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.
May 23 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.