Click here to Skip to main content
15,917,875 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i have a table like this..
ID TravelBy Travelamount
1 Car/Bike 5/6
2 train/bus 7/8

if i want data based on Id ..then data should be comes like this.

ID TravelBy Travelamount
1 Car 5
Bike 6
2 train 7
bus 8

What I have tried:

i dont know what query should be used to fetch this output.
it split the date by forward slash..and showing in next line in same row
Updated 28-Jul-17 1:43am
Richard Deeming 12-May-17 9:14am    
This was an extremely bad database design when you asked last week[^], and it's still extremely bad today.

Do yourself a favour: stop battling with the current design, and take the time to properly normalize your database. Once you've done that, the query will be trivial.
CHill60 12-May-17 16:10pm    
I've already answered this once. Don't repost it again

How to split column data and show in next line in SQL server

There's no way to display data in new line. You can only split data to show that data in next row!

The best way is to use CTE[^] or custom function[^]. See:
How a split a String using delimiter in Sql[^]
get 2nd and 3rd string by splitting a long string using T-Sql[^]

Finall note: your database structure/design is very bad. You have to change it, as Richard Deeming[^] mentioned in the comment to the question.
Share this answer
in sql by default has split function,
SELECT * FROM [dbo].[Split]('1 Car/Bike 5/6', '/')
Share this answer
CHill60 28-Jul-17 11:20am    
SQL 2016 onwards does indeed have a built-in function for splitting strings but it is not called "Split"!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900