Separate one column in many columns

Question

Post reply

Separate one column in many columns

Pelon

SSC Enthusiast

Points: 183
More actions
June 3, 2013 at 9:51 pm

#274595

I have a table with the next structure:
CREATE TABLE students
(
ID_Student INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name nchar(30),
rate nchar(5)
)
and i have some data into the table like:
(Name, rate) ('Chipper Jones', '678*9')
(Name, rate) ('Mike Piazaa', '98***')
(Name, rate) ('Barry Bonds', '678**')
(Name, rate) ('Larry Walker', '*88*9')
Now I want separate the column (rate) in five columns more depending each number,
at the same time change the (*) for (10):
something like this:
game1 game2 game3 game4 game5
6 7 8 10 9
Thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Something like this..

SELECT*,

CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,

CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,

CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,

CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,

CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5

FROMstudents

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Pelon SSC Enthusiast Points: 183 More actions · Answer 2

Kingston Dhasian (6/4/2013)
Something like this..
SELECT*,
CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,
CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,
CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,
CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,
CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5
FROMstudents

That's amazing ! it really Works.

thank you so much.

Pelon SSC Enthusiast Points: 183 More actions · Answer 3

Thanks for your help.

Now I have a doubt, I would like to add the numbers of each column and after add them i want divide them to get the percent, for example:

game1 game2 game3 game4 game5 percent

8 + 9 + 7 + 8 + 10 / 8.4

Thanks in advance.

Pelon SSC Enthusiast Points: 183 More actions · Answer 4

Pelon (6/4/2013)
Kingston Dhasian (6/4/2013)
Something like this..
SELECT*,
CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,
CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,
CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,
CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,
CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5
FROMstudents
That's amazing ! it really Works.
thank you so much.
Thanks for your help.
Now I have a doubt, I would like to add the numbers of each column and after add them i want divide them to get the percent, for example:
game1 game2 game3 game4 game5 percent
8 + 9 + 7 + 8 + 10 / 8.4
Thanks in advance.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

You can use a CTE or a Derived table like this

; WITH cte_Students AS

(

SELECT*,

CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,

CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,

CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,

CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,

CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5

FROMstudents

)

SELECT*, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent]

FROMcte_Students

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Pelon SSC Enthusiast Points: 183 More actions · Answer 6

Kingston Dhasian (6/12/2013)
You can use a CTE or a Derived table like this
; WITH cte_Students AS
(
SELECT*,
CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,
CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,
CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,
CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,
CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5
FROMstudents
)
SELECT*, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent]
FROMcte_Students
Thanks for you prompt response. Let me tell you that the query has showed a diferent result.
for example:
id name game1 game2 game3 game4 game5 percent
3Larry Walker 10998721997
the result should be 8.6
or may be I am doing something wrong.
I appreciate your attention.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Kingston Dhasian (6/4/2013)
Something like this..
SELECT*,
CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,
CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,
CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,
CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,
CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5
FROMstudents

🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

It happened because the data types for columns game1, game2,...game5 were taken as VARCHAR

The below code will avoid the issue

; WITH cte_Students AS

(

SELECT*,

CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN 10 /*The previous version had apostrophes here which made the data type of the column as VARCHAR*/ ELSE SUBSTRING(rate, 1, 1) END AS game1,

CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 2, 1) END AS game2,

CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 3, 1) END AS game3,

CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 4, 1) END AS game4,

CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 5, 1) END AS game5

FROMstudents

)

SELECT*, ( game1 + game2 + game3 + game4 + game5 ) / 5.0 /*Made 5 as 5.0 to avoid integer conversion*/ AS [percent]

FROMcte_Students

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

-- sample data

;WITH students (ID_Student, Name, rate) AS (

SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALL

SELECT 2, 'Mike Piazaa', '98***' UNION ALL

SELECT 3, 'Barry Bonds', '678**' UNION ALL

SELECT 4, 'Larry Walker', '*88*9'

)

-- solution

SELECT

s.ID_Student, s.Name, s.rate,

x.game1, x.game2, x.game3, x.game4, x.game5,

(x.game1 + x.game2 + x.game3 + x.game4 + x.game5) / 5.0 AS [percent]

FROM students s

CROSS APPLY (

SELECT

Game1 = CAST(REPLACE(SUBSTRING(rate, 1, 1),'*','10') AS INT),

Game2 = CAST(REPLACE(SUBSTRING(rate, 2, 1),'*','10') AS INT),

Game3 = CAST(REPLACE(SUBSTRING(rate, 3, 1),'*','10') AS INT),

Game4 = CAST(REPLACE(SUBSTRING(rate, 4, 1),'*','10') AS INT),

Game5 = CAST(REPLACE(SUBSTRING(rate, 5, 1),'*','10') AS INT)

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Pelon SSC Enthusiast Points: 183 More actions · Answer 10

Kingston Dhasian (6/13/2013)
It happened because the data types for columns game1, game2,...game5 were taken as VARCHAR
The below code will avoid the issue
; WITH cte_Students AS
(
SELECT*,
CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN 10 /*The previous version had apostrophes here which made the data type of the column as VARCHAR*/ ELSE SUBSTRING(rate, 1, 1) END AS game1,
CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 2, 1) END AS game2,
CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 3, 1) END AS game3,
CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 4, 1) END AS game4,
CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN 10 ELSE SUBSTRING(rate, 5, 1) END AS game5
FROMstudents
)
SELECT*, ( game1 + game2 + game3 + game4 + game5 ) / 5.0 /*Made 5 as 5.0 to avoid integer conversion*/ AS [percent]
FROMcte_Students
I am very grateful to you for your kind attention to my questions.
Let me tell you that the query works. But when returns the result it show me five numbers more.
For example the query returns:
Name game1 game2 game3 game4 game5 percent
Larry Walker 10 9 9 87 8.600000
I would like the results were like: 8.6, 9.0, 10, 8.8
thanks in advance.

Pelon SSC Enthusiast Points: 183 More actions · Answer 11

ChrisM@Work (6/13/2013)
-- sample data ;WITH students (ID_Student, Name, rate) AS ( SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALL SELECT 2, 'Mike Piazaa', '98***' UNION ALL SELECT 3, 'Barry Bonds', '678**' UNION ALL SELECT 4, 'Larry Walker', '*88*9' ) -- solution SELECT s.ID_Student, s.Name, s.rate, x.game1, x.game2, x.game3, x.game4, x.game5, (x.game1 + x.game2 + x.game3 + x.game4 + x.game5) / 5.0 AS [percent] FROM students s CROSS APPLY ( SELECT Game1 = CAST(REPLACE(SUBSTRING(rate, 1, 1),'*','10') AS INT), Game2 = CAST(REPLACE(SUBSTRING(rate, 2, 1),'*','10') AS INT), Game3 = CAST(REPLACE(SUBSTRING(rate, 3, 1),'*','10') AS INT), Game4 = CAST(REPLACE(SUBSTRING(rate, 4, 1),'*','10') AS INT), Game5 = CAST(REPLACE(SUBSTRING(rate, 5, 1),'*','10') AS INT) ) x
Thanks for your kind reply .
But the result show me something like 8.600000
I would like it returns 8.6
Thanks in advance.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 12

Change the final SELECT statement to something like this

SELECT*, CAST( ( game1 + game2 + game3 + game4 + game5 ) / 5.0 AS NUMERIC(10,1) ) AS [percent]

FROMcte_Students

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Pelon SSC Enthusiast Points: 183 More actions · Answer 13

Kingston Dhasian (6/14/2013)
Change the final SELECT statement to something like this
SELECT*, CAST( ( game1 + game2 + game3 + game4 + game5 ) / 5.0 AS NUMERIC(10,1) ) AS [percent]
FROMcte_Students
Thanks for your kind reply
I am very grateful.