sql server programming: Want to create something like this. Please help! -- sql server programming

Want to create something like this. Please help!

Rex
9/12/2007 11:17:46 PM

sql server programming:

Below is the format of table I have with some sample data.

familyID MemberName marker
allele1
allele2
-------- ------------ -----------
-----------------
-----------------
502 Angus 01-D8S1179
12.0 14.0
502 Angus 02-D21S11
28.0 31.0
502 Angus 03-D7S820
10.0 12.0
502 Angus 05-D3S1358
16.0 18.0
502 Angus 07-D13S317
12.0 14.0
502 Angus 11-vWA
14.0 16.0
502 Angus 13-D18S51
12.0 13.0
502 Angus 14-AMEL
1.0 0.0
502 Angus 15-D5S818
12.0 12.0
502 Angus 16-FGA
19.0 20.0
502 Liam 01-D8S1179
12.0 14.0
502 Liam 02-D21S11
28.0 31.0
502 Liam 03-D7S820
10.0 12.0
502 Liam 05-D3S1358
16.0 18.0
502 Liam 07-D13S317
12.0 14.0
502 Liam 11-vWA
14.0 16.0
502 Liam 13-D18S51
12.0 13.0
502 Liam 14-AMEL
1.0 0.0
502 Liam 15-D5S818
12.0 13.0
502 Liam 16-FGA
19.0 20.0

What I want to do is create a query or a report or anything that shows
me records in the following format.

Angus Liam
------------------------------------------------------------------------------------------------------------------------------------------
502 01-D8S1179 12.0
14.0 12.0 14.0
502 02-D21S11 28.0
31.0 28.0 31.0
502 03-D7S820 10.0
12.0 10.0 12.0
502 05-D3S1358 16.0
18.0 16.0 18.0
502 07-D13S317 12.0
14.0 12.0 14.0
502 11-vWA 14.0
16.0 14.0 16.0
502 13-D18S51 12.0
13.0 12.0 13.0
502 14-AMEL 1.0
0.0 1.0 0.0
502 15-D5S818 12.0
12.0 12.0 13.0
502 16-FGA 19.0
20.0 19.0 20.0

I am using SQL Server 2000

Any help would be greatly appreciated.

Thanks.

Re: Want to create something like this. Please help!

Pierpaolo Simoncini
9/13/2007 12:00:00 AM

Hi Rex,

it's classic cross tab problem resolved in Sql Server 2005 with PIVOT
operator. Search for CROSS TAB in SQL 2000 onto Google to see many
possible solutions. If your target it's produce a report via SSRS you
can use builted-in features to solve the problem in report environment.

My Best Regards

Pierpaolo Simoncini
MCP Sql Server 2000
ITALY

Rex ha scritto:
[quoted text, click to view]

Re: Want to create something like this. Please help!

Hugo Kornelis
9/15/2007 12:00:00 AM

[quoted text, click to view]

Hi Rex,

If I understand the requirement correctly, you need something like this:

SELECT familyID, marker,
MAX(CASE WHEN MemberName = 'Angus' THEN allele1 END) AS Angus,
MAX(CASE WHEN MemberName = 'Liam' THEN allele1 END) AS Liam
FROM YourTable
GROUP BY familyID, marker;

--
Hugo Kornelis, SQL Server MVP

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.