How to separate single column in to columns of database record in SQL?

I have a database table in sql server 2008, the table has a column balls with 20 balls as an example. i want to divide the balls in to red and non red color balls in in a separate columns . i.e 5 red balls in a column, in another and 15 black balls in another column.

All the balls are contain in a single column of a table previously . My query is as below

;with r as 
(
SELECT [balls] FROM [balls_table]
)
SELECT (SELECT balls
FROM [balls_table]
WHERE color LIKE '%red%') a,
(SELECT balls
FROM [balls_table]. 
WHERE color NOT LIKE '%red%') as b
 FROM r

I thought i can select the whole balls first, and then select red and non red from the first select as i did above. The above query return an exception saying a sub query returns more one one record. Pls , any help would be appreciated

Answers


Try using CASE WHEN like this :

SELECT CASE WHEN color LIKE '%red%' THEN [balls] ELSE NULL END AS RedBalls,
       CASE WHEN color NOT LIKE '%red%' THEN [balls] ELSE NULL END AS NonRedBalls
FROM [balls_table]

You can get total red and non red balls by SUM like below :

SELECT SUM(RedBalls) AS RedBalls , SUM(NonRedBalls) AS NonRedBalls
FROM
(
SELECT CASE WHEN color LIKE '%red%' THEN [balls] ELSE NULL END AS RedBalls,
       CASE WHEN color NOT LIKE '%red%' THEN [balls] ELSE NULL END AS NonRedBalls
FROM [balls_table]
) tt

SQL Fiddle Demo


Try this

Select
CASE WHEN color = 'Red' THEN Color ELSE NULL END As Color1,
CASE WHEN color <> 'Red' THEN Color ELSE NULL END As Color2
from balls

SQLFiddle Demo


Need Your Help

Google apps script - Speeding up getActiveDocument()?

performance google-apps-script google-docs

I'm working on a script to set to uppercase a selected text in a Google document. The script works ok, but it is extremely slow in a large document. So I stripped down almost everything to perform ...

Creating a new rrd database based on an existing one

clone reverse-engineering rrdtool rrd

I have some old rrdtool databases, for which the exact creation recipe has long been since lost. I need to create a new database with the same characteristics as the current ones. I've dumped a cou...