Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cross-tab insert in SQL Server

Posted on 2011-02-22
7
Medium Priority
?
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 300 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 

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 1200 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 1200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

660 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