

Grasshopper
Group: General Forum Members
Last Login: Tuesday, September 15, 2015 9:31 AM
Points: 13,
Visits: 63


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.




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:31 AM
Points: 2,866,
Visits: 4,893


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 FROM 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/




Grasshopper
Group: General Forum Members
Last Login: Tuesday, September 15, 2015 9:31 AM
Points: 13,
Visits: 63


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 FROM students
That's amazing ! it really Works. thank you so much.




Grasshopper
Group: General Forum Members
Last Login: Tuesday, September 15, 2015 9:31 AM
Points: 13,
Visits: 63


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.




Grasshopper
Group: General Forum Members
Last Login: Tuesday, September 15, 2015 9:31 AM
Points: 13,
Visits: 63


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 FROM students 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.




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:31 AM
Points: 2,866,
Visits: 4,893


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 FROM students )
SELECT *, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent] FROM cte_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/




Grasshopper
Group: General Forum Members
Last Login: Tuesday, September 15, 2015 9:31 AM
Points: 13,
Visits: 63


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 FROM students )
SELECT *, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent] FROM cte_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 3 Larry Walker 10 9 9 8 7 21997 the result should be 8.6 or may be I am doing something wrong. I appreciate your attention.




SSCForever
Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 40,989,
Visits: 38,287


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 FROM students
Jeff Moden "RBAR is pronounced "reebar" and is a "Modenism" for "RowByAgonizingRow".
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."
Helpful Links: How to post code problems How to post performance problems




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:31 AM
Points: 2,866,
Visits: 4,893


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 FROM students )
SELECT *, ( game1 + game2 + game3 + game4 + game5 ) / 5.0 /*Made 5 as 5.0 to avoid integer conversion*/ AS [percent] FROM cte_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/




SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 8,306,
Visits: 18,017


 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 Exploring Recursive CTEs by Example Dwain Camps



