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 Monday, June 3, 2013 9:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.
Post #1459523
Posted Tuesday, June 4, 2013 1:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1459572
Posted Tuesday, June 4, 2013 7:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.

Post #1460005
Posted Tuesday, June 11, 2013 7:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.
Post #1462434
Posted Tuesday, June 11, 2013 7:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.

Post #1462435
Posted Wednesday, June 12, 2013 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1462509
Posted Wednesday, June 12, 2013 8:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.




Post #1462884
Posted Wednesday, June 12, 2013 9:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 36,760, Visits: 31,215
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 "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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462891
Posted Thursday, June 13, 2013 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1462929
Posted Thursday, June 13, 2013 3:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
-- 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
Post #1462947
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse