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

Multiple @ServiceActivator methods with an implicit payload-type-routing

java spring spring-integration dynamic-dispatch

Is there an elegant way to define a single @MessageEndpoint bean with multiple @ServiceActivator methods (or something like that), where the methods' argument types are implicitly used as a

Dynamic Navigation with Mustache Templating

php html codeigniter mustache.php

I have created a template with mustache.php and am wanting to dynamically create my navigation based on files within a single folder. I am using codeigniter for this project.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.