splitting a column into several columns.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

splitting a column into several columns.

Author

Topic

Azaera
Starting Member

5 Posts

Posted - 2009-05-11 : 10:57:26

Hiya.

I'm trying to learn SQL (using mssql 05) and yes, this is a schoolquestion so I don't want the code, just some kicks into the right direction.
The question I have is that I got one large column with several years listed, like:

1995
1997
1994
1999
2001
2004
1997
2005

and so on.

Now I need to break this and list it in five columns with some years in all columns, like:

-95, 95-97, 98-00, 01-03, 04-

So far I've tried with several statements, but the best I've been able to come up with is:

SELECT COUNT(YEAR) AS '-95' FROM Movie
WHERE YEAR < 1995

SELECT COUNT(YEAR) AS '95-97' FROM Movie
WHERE YEAR BETWEEN 1995 AND 1997

SELECT COUNT(YEAR) AS '98-00' FROM Movie
WHERE YEAR BETWEEN 1998 AND 2000

SELECT COUNT(YEAR) AS '01-03' FROM Movie
WHERE YEAR BETWEEN 2001 AND 2003

SELECT COUNT(YEAR) AS '04-' FROM Movie
WHERE YEAR >= 2004;

But the output isn't what I want, and I've tried using a nested query but that just stated incorrect syntax near FROM, and the unions lists all in the first column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:00:31

you need a single select with case when inside count like


SELECT COUNT(CASE WHEN YEAR<1995 THEN YEAR ELSE NULL END) AS '-95',
COUNT(CASE WHEN YEAR>=1995 AND YEAR<1997 THEN YEAR ELSE NULL END) AS '95-97' ,
...
FROM Movie

Azaera
Starting Member

5 Posts

Posted - 2009-05-11 : 11:08:38

Ahh, thank you, worked like a charm :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:13:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources