Cross-tab insert in SQL Server

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?
koossaAsked:
Who is Participating?
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
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
 
Mark BradyPrincipal Data EngineerCommented:
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
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rajeevnandanmishraConnect With a Mentor Commented:
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
 
koossaAuthor Commented:
Hi, yes, pivot is what I want to use, but I cannot figure out how to implement it for my example?
0
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
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
 
koossaAuthor Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.