

Grasshopper
Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11,
Visits: 46


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: Thursday, April 10, 2014 5:09 AM
Points: 2,567,
Visits: 4,654


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: Monday, June 17, 2013 7:46 PM
Points: 11,
Visits: 46


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: Monday, June 17, 2013 7:46 PM
Points: 11,
Visits: 46


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: Monday, June 17, 2013 7:46 PM
Points: 11,
Visits: 46


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: Thursday, April 10, 2014 5:09 AM
Points: 2,567,
Visits: 4,654


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: Monday, June 17, 2013 7:46 PM
Points: 11,
Visits: 46


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.




SSCDedicated
Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950,
Visits: 30,232


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."
"Change is inevitable. Change for the better is not."  04 August 2013 (play on words) "Just because you CAN do something in TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Helpful Links: How to post code problems How to post performance problems




SSCrazy
Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567,
Visits: 4,654


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/




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748,
Visits: 12,843


 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



