Pivot Table (no calculation) - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Pivot Table (no calculation)

Author

Topic

JGALFO21
Starting Member

4 Posts

Posted - 2004-11-20 : 12:50:57

Hi. I need to come up with a tabulated query that has no calculations in MSSQL 2000

My data in a view looks like this:
ObjectID | AttributeID | ExpData
O1 | A | 1/1/04
O1 | B | 2/2/05
O1 | C | NULL
O1 | D | H1B
O2 | A | 3/3/05
O2 | B | NULL
O2 | D | LPR
O3 | D | L1
etc...
I'd like my new query to show the tabulation as follows:
ObjectID | A | B | C | D
O1 | 1/1/04 | 2/2/05 | NULL | H1B
O3 | NULL | NULL | NULL | L1
O2 | 3/3/05 | NULL | NULL | LPR

Your help is greatly appreciated.
Thanks.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 13:40:45

[code]
declare @v table(objectid char(2), attributeid char(1), expdata char(6) null)
insert @v(objectid,attributeid,expdata)
select '01','A','1/1/04' union all
select '01','B','2/2/05' union all
select '01','C',null union all
select '01','D','H1B' union all
select '02','A','3/3/05' union all
select '02','B',null union all
select '02','D','LPR' union all
select '03','D','L1'

--select * from @v

select coalesce(a.objectid,b.objectid,c.objectid,d.objectid) as objectid, min(A) as A, min(B) as B, min(C) as C, min(D) as D from
( select objectid, expdata as A from @v where attributeid = 'A' ) a
full join
( select objectid, expdata as B from @v where attributeid = 'B' ) b on a.objectid = b.objectid
full join
( select objectid, expdata as C from @v where attributeid = 'C' ) c on a.objectid = c.objectid
full join
( select objectid, expdata as D from @v where attributeid = 'D' ) d on a.objectid = d.objectid
group by coalesce(a.objectid,b.objectid,c.objectid,d.objectid)
[/code]

rockmoose

JGALFO21
Starting Member

4 Posts

Posted - 2004-11-20 : 14:24:03

Thanks rockmoose!

That seems to do it... (Your rock! you don't know how appreciated I am :-) )
I'll try it with my real data now...

Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-20 : 14:42:15

ah! no full outer joins!


select objectId,
  Max(case when attributeid = 'A' then expdata else Null end) as A,
  Max(case when attributeid = 'B' then expdata else Null end) as B,
  Max(case when attributeid = 'C' then expdata else Null end) as C,
  Max(case when attributeid = 'D' then expdata else Null end) as D
from 
  @v
group by 
  objectid

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 14:51:45

You are welcome,
the solution assumes that objectid+attributeid are unique.

this one is probably more efficient, try them both:

declare @v table(objectid char(2), attributeid char(1), expdata char(6) null)
insert @v(objectid,attributeid,expdata)
select '01','A','1/1/04' union all
select '01','B','2/2/05' union all
select '01','C',null union all
select '01','D','H1B' union all
select '02','A','3/3/05' union all
select '02','B',null union all
select '02','D','LPR' union all
select '03','D','L1'

--select * from @v

select
	objectid,
	min(case attributeid when 'A' then expdata end) as A,
	min(case attributeid when 'B' then expdata end) as B,
	min(case attributeid when 'C' then expdata end) as C,
	min(case attributeid when 'D' then expdata end)as D
from @v
group by objectid

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 14:53:17

Hi Jeff,
we even got the exact same syntax

- well almost.. edit
was just wondering when someone would post that...!

rockmoose

JGALFO21
Starting Member

4 Posts

Posted - 2004-11-20 : 15:09:10

Thanks Guys... simplicity there is great... it solved my other issue of adding more columns to the result set much faster. (I am now a big fan of this site) :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources