Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Separate one column in many columns Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 6:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46
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
FROM students
)

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


I would like the results were like: 8.6, 9.0, 10, 8.8

thanks in advance.



Post #1463362
Posted Thursday, June 13, 2013 6:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46
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.




Post #1463363
Posted Friday, June 14, 2013 3:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
Change the final SELECT statement to something like this

SELECT	*, CAST( ( game1 + game2 + game3 + game4 + game5 ) / 5.0 AS NUMERIC(10,1) ) 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/
Post #1463454
Posted Monday, June 17, 2013 7:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46
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]
FROM cte_Students



Thanks for your kind reply
I am very grateful.



Post #1464434
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse