SQL results in one row from two

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL results in one row from two

Author

Topic

after17
Starting Member

1 Post

Posted - 2009-05-22 : 14:44:42

I have a table setup like this:

company category partner ownership
----------- -------- ----------- ----------
841796 11 812 16.5000
841796 11 1249 16.5000
841796 11 1191 16.5000
841796 11 606 16.5000
841796 11 865 12.0000
841796 11 941 12.0000
841796 11 1233 10.0000
841796 17 812 16.5000
841796 17 1249 14.5000
841796 17 1191 18.5000
841796 17 606 16.5000
841796 17 865 12.0000
841796 17 941 02.0000
841796 17 1233 20.0000

I want to be able to query this data by company/partner, and return results on one row somehow? Like this:

company category1 partner ownership1 category2 ownership2
----------- -------- ----------- -----------------------------------
841796 11 812 16.5000 17 16.5000
841796 11 1249 16.5000 17 14.5000
841796 11 1191 16.5000 17 18.5000
841796 11 606 16.5000 17 16.5000
841796 11 865 12.0000 17 12.0000
841796 11 941 12.0000 17 02.0000
841796 11 1233 10.0000 17 20.0000

So I'm grouping it by company, partner, but I can't figure out how to do that? A temp table somehow, or using 'partition by' somehow to create a rowNum based on company, partner?

kira
Starting Member

17 Posts

Posted - 2009-05-22 : 16:36:51

I am a newbie in SQL. I don't know whether this is helpful.
[CODE]
create table #temp
(
company char(10),
category char(10),
partner char(10),
ownership money
)

insert into #temp
select '841796','11','812',16.5000
union
select '841796', '11', '1249', 16.5000
union
select '841796','11','1191',16.5000
union
select '841796', '11', '606', 16.5000
union
select '841796', '17', '812', 16.5000
union
select '841796', '17', '1249', 14.5000
union
select '841796', '17', '1191', 18.5000
union
select '841796', '17', '606', 16.5000

create table #temp2
( category char(10),
partner char(10),
cat1 char(10),
share1 money,
cat2 char(10),
share2 money
)

insert into #temp2
select a.company,a.partner,a.category,a.ownership,b.category,b.ownership
from
(select
company,
partner,
category,
ownership,
rank() over (partition by company,partner order by ownership,category) as rank
from #temp
) a
inner join
(select
company,
partner,
category,
ownership,
rank() over (partition by company,partner order by ownership,category) as rank
from #temp
) b
on a.company=b.company and a.partner=b.partner
where a.rank=1
and b.rank=2

select * from #temp2
drop table #temp
drop table #temp2
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 10:57:17

[code]SELECT company,partner,
MAX(CASE WHEN seq=1 THEN category ELSE NULL END) AS category1,
MAX(CASE WHEN seq=1 THEN ownership ELSE NULL END) AS ownership1,
MAX(CASE WHEN seq=2 THEN category ELSE NULL END) AS category2,
MAX(CASE WHEN seq=2 THEN ownership ELSE NULL END) AS ownership2
FROM
(
SELECT company, category, partner, ownership,
row_number() over (partition by company,partner order by category) as seq
FROM YourTable
)t
GROUP BY company,partner
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources