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.
| Author |
Topic |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
 Posted - 2014-11-11 : 14:53:00
|
Hi friends,
I have a table Customer in the following way
CustomerNumber CustmerType
aaa1 1-Regular,2-Daily,3.5-Yearly
bbb1 0.5 Regual, 1.5-daily, 4-Yearly
Now i want to split the Customer Type into The fields
Regular, Daily and Yearly for the Customer number and show the values..
How can i achieve it...Thank you in advance |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-11 : 15:52:12
|
| try the string splitter function: http://www.sqlservercentral.com/articles/Tally+Table/72993/ |
 |
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2014-11-11 : 17:43:55
|
Thank You gbritton but i could achieve my target using the Query below: Ofcourse i Customized it to my requirements..so anyone looking for similar question please try this..
;WITH Split_Names (Value,Name, xmlname)
AS
(
SELECT Value,
Name,
CONVERT(XML,'<Names><name>'
+ REPLACE(Name,',', '</name><name>') + '</name></Names>') AS xmlname
FROM tblnames
)
SELECT Value,
xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name,
xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname
FROM Split_Names |
|
|
|
|
|
|
split a field value to three fields