Solved

Cross-tab insert in SQL Server

Posted on 2011-02-22
7
356 Views
Last Modified: 2012-05-11
I've got 2 SQL Server tables.

1st table is my source table called [Animals]

[ID Number], [Camp], [Age]
1234              Camp1  0 to 7 months
2345              Camp1  8 to 12 months
..................................................
..................................................
5555              Camp2  8 to 12 months


I want to crosstab this data to the 2nd destination table

[Camp],  [0 to 7 Months], [8 to 12 months], ...., ....., .....
Camp 1                         1                          1
Camp 2                         0                          1

Where Camp is the camp name, eg Camp1, Camp 2 and the other columns is the number of animals in that camp.

Any ideas how I can transfer the data from my source table to the destination table with a query?
0
Comment
Question by:koossa
7 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34950053
I'm sure there are many ways to achieve this but here is one that comes to mind.

$sql = "SELECT `Camp`,`Age` FROM `animals`";
$result = mysql_query($sql)or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$camp = "{$row['Camp']}";
$age = "{$row['age']}";
if($age == "0 to 7 Months"){
mysql_query("UPDATE `destination` SET `0 to 7 Months` = `0 to 7 Months` + 1 WHERE `Camp` = '$camp'")or die(mysql_error());
}else{
mysql_query("UPDATE `destination` SET `8 to 12 Months` = `8 to 12 Months` + 1 WHERE `Camp` = '$camp'")or die(mysql_error());
}
}

// That should do it. Make sure you have a table called "destination" for the example and it should read your first table and depending upon the age of each entry, it will update or increment the number in the destination table by 1.

I didn't know if each entry in the first table means 1 animal per entry or what? If you want to be more specific then post a bigger sample of your table and list all column names. Hope this helps
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34950185
I would suggest a small Insert Query

insert into #CampBirths
select      Camp,
            sum(case when Age='0 to 7 Months' then 1 else 0 end) as Age_00_07_Months,
            sum(case when Age='8 to 12 months' then 1 else 0 end) as Age_08_12_Months,
            sum(case when Age='0 to 3 Years' then 1 else 0 end) as Age_01_03_Years,
            sum(case when Age='4 to 7 Years' then 1 else 0 end) as Age_04_07_Years,
            sum(case when Age='8 to 10 Years' then 1 else 0 end) as Age_08_10_Years
from #Animals
group by Camp

beneath a full example
 
create table #Animals
(
	id int identity(1,1),
	Camp varchar(255),
	Age varchar(30)
)

create table #CampBirths
(
	Camp varchar(255),
	Age_00_07_Months int,
	Age_08_12_Months int,
	Age_01_03_Years int,
	Age_04_07_Years int,
	Age_08_10_Years int,
	
)

insert into #Animals select 'Camp1', '0 to 7 Months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp2', '0 to 7 Months'
insert into #Animals select 'Camp2', '8 to 12 months'

insert into #CampBirths
select	Camp, 
		sum(case when Age='0 to 7 Months' then 1 else 0 end) as Age_00_07_Months, 
		sum(case when Age='8 to 12 months' then 1 else 0 end) as Age_08_12_Months, 
		sum(case when Age='0 to 3 Years' then 1 else 0 end) as Age_01_03_Years, 
		sum(case when Age='4 to 7 Years' then 1 else 0 end) as Age_04_07_Years, 
		sum(case when Age='8 to 10 Years' then 1 else 0 end) as Age_08_10_Years
from #Animals
group by Camp

select * from #CampBirths

drop table #Animals
drop table #CampBirths

Open in new window

0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 100 total points
ID: 34950393
Hi Koossa,

You can use the PIVOT syntax in SQL 2005 onwards. It is quite a good to work with. You also need not to have the destination table created before hand.

Please try at once, and If you require any help in the syntax of PIVOT please respond.

rnm


0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:koossa
ID: 34950452
Hi, yes, pivot is what I want to use, but I cannot figure out how to implement it for my example?
0
 
LVL 10

Accepted Solution

by:
John Claes earned 400 total points
ID: 34951414
Koossa


You can then use the folowing query

select      *
into #CampBirths
from #Animals
PIVOT
(
  count(Age )
  FOR Age IN ([0 to 7 Months],[8 to 12 months],[0 to 3 Years],[4 to 7 Years],[8 to 10 Years])
)
as p
select * from #CampBirths
create table #Animals
(
	Camp varchar(255),
	Age varchar(30)
)

insert into #Animals select 'Camp1', '0 to 7 Months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp2', '0 to 7 Months'
insert into #Animals select 'Camp2', '8 to 12 months'

select	*
into #CampBirths
from #Animals
PIVOT
(
  count(Age ) 
  FOR Age IN ([0 to 7 Months],[8 to 12 months],[0 to 3 Years],[4 to 7 Years],[8 to 10 Years])
)
as p
select * from #CampBirths

drop table #Animals
drop table #CampBirths

Open in new window

0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 400 total points
ID: 34951428
And in Case you want to stay using the Identity upon the animals Table

select      *
into #CampBirths
from (select camp,age from #Animals) as T
PIVOT
(
  count(Age )
  FOR Age IN ([0 to 7 Months],[8 to 12 months],[0 to 3 Years],[4 to 7 Years],[8 to 10 Years])
)
as p
select * from #CampBirths

 
create table #Animals
(
	id int identity(1,1),
	Camp varchar(255),
	Age varchar(30)
)

insert into #Animals select 'Camp1', '0 to 7 Months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp1', '8 to 12 months'
insert into #Animals select 'Camp2', '0 to 7 Months'
insert into #Animals select 'Camp2', '8 to 12 months'

select	*
into #CampBirths
from (select camp,age from #Animals) as T
PIVOT
(
  count(Age ) 
  FOR Age IN ([0 to 7 Months],[8 to 12 months],[0 to 3 Years],[4 to 7 Years],[8 to 10 Years])
)
as p
select * from #CampBirths

drop table #Animals
drop table #CampBirths

Open in new window


regards

poor beggar
0
 

Author Closing Comment

by:koossa
ID: 34959346
Thank you
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now