Solved

Cross-tab insert in SQL Server

Posted on 2011-02-22
7
383 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query 18 35
How can I get the entire database script? 7 12
job schedule 8 17
MS SQL + group by time 4 14
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

809 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