CODEWITHSUNDEEP
sqlsql-serversql-server-2000
kev670
kev670

Reputation: 880

Creating multiple columns from one column

My database is structured like this:

Number | Month | Country
------------------------
63     | June  | Ireland
48     | June  | England
55     | June  | Spain
66     | May   | Ireland
33     | May   | England
53     | May   | Spain
44     | April | Ireland
44     | April | England
44     | April | Spain

I want to use an SQL statement to call this from the data above. Can anyone help me out please. Basically I want to split the number column into multiple other columns according to the month associated with it. I'm using sql-server 2000

Country | June | May | April
---------------------------
Ireland | 63   | 66  |  44
England | 48   | 33  |  44
Spain   | 55   | 53  |  44

Upvotes: 2

Views: 21053

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13486

Try this,Here this query will give the proper results irrespective of the unique number of months.It means there is no need to hard code the months name as column names.

CREATE TABLE tbl1(Number int,Months varchar(10),Country varchar(10))
INSERT INTO tbl1
VALUES(63,'June','Ireland'),
(48,'June','England'),
(55,'June','Spain'),
(66,'May','Ireland'),
(33,'May','England'),
(53,'May','Spain'),
(44,'April','Ireland'),
(44,'April','England'),
(44,'April','Spain')

DECLARE @month_colms varchar(100)
DECLARE @sqlstr varchar(1000)
DECLARE @colname varchar(100)
select @month_colms=STUFF((select ', '+months from (select months from tbl1 group by months) a for xml path('')),1,1,'')
SET @sqlstr=''
While(CHARINDEX(',',@month_colms,1)>0)
BEGIN
SELECT @colname=LEFT(ltrim(rtrim(@month_colms)),CHARINDEX(',',ltrim(rtrim(@month_colms)),1)-1)
SET @month_colms=RIGHT(ltrim(rtrim(@month_colms)),LEN(ltrim(rtrim(@month_colms)))-CHARINDEX(',',ltrim(rtrim(@month_colms)),1))
SET @sqlstr=@sqlstr+','+'sum(case when Months = '''+@colname+''' then Number end) as '''+@colname+''''
END

SET @sqlstr=@sqlstr+','+'sum(case when Months = '''+ltrim(rtrim(@month_colms))+''' then Number end) as '''+@month_colms+''''

exec('select  Country'+@sqlstr+' from tbl1 group by Country')

Upvotes: 2

Andomar
Andomar

Reputation: 238078

That process is called pivoting. One way to do it:

 select  Country
 ,       sum(case when Month = 'June' then Number end) as June
 ,       sum(case when Month = 'May' then Number end) as May
 ,       sum(case when Month = 'April' then Number end) as April
 from    YourTable
 group by
         Country

Upvotes: 9

Related Questions