Solved

Cross-tab insert in SQL Server

Posted on 2011-02-22
7
393 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL eating up memory? 16 37
Linked Server - SP with Param to VIew 7 21
.NET - find out if application is running on Windows 2012 server 3 32
Need SSIS project 2 23
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 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