Back in the saddle? A grid/pivot/? question

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)

Back in the saddle? A grid/pivot/? question

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2008-09-03 : 04:01:58

Hi, everyone. I used to be fairly active here. But then life changed and I wasn't working with SQL so much. And, well, now I'm back. And even the basics are giving me some trouble.

I've got a simple 3 table schema, with two main tables and a join table, in the form:

create table people (PersonId int identity, PersonName varchar(50))
create table attributes (AttributeId int identity, AttributeName varchar(50))
create table people_attributes (PersonId int, AttributeId int, degree int)

So far so good. Let's add some data:

insert into people (PersonName) values ('Joe')
insert into people (PersonName) values ('Sally')
insert into people (PersonName) values ('Tom')
insert into attributes (AttributeName) values('happy')
insert into attributes (AttributeName) values('sad')
insert into attributes (AttributeName) values('weird')
insert into people_attributes (PersonId,AttributeId,degree) values (1,1,1)
insert into people_attributes (PersonId,AttributeId,degree) values (1,2,5)
insert into people_attributes (PersonId,AttributeId,degree) values (1,3,3)
insert into people_attributes (PersonId,AttributeId,degree) values (2,2,4)
insert into people_attributes (PersonId,AttributeId,degree) values (2,3,2)
insert into people_attributes (PersonId,AttributeId,degree) values (3,1,1)
insert into people_attributes (PersonId,AttributeId,degree) values (3,3,2)

Now, I know this should be easy, but I just cannot get my head around it. I want a simple grid of the data, pretty similar to those sample inserts. Something like:


          Joe    Sally    Tom
Happy        1              1
Sad          5      4       
Weird        3      2       2

Of course, I want it to be dynamic, with a single query growing to a grid of count(People) x count(Attributes).

I can get the basic list easily enough (of course adjusting outer joins and using IsNull and stuff to fill in blank rows as desired) with something like

select p.PersonName,a.AttributeName,pa.degree
 from attributes a 
 join people_attributes pa on a.AttributeId=pa.AttributeId
 join peple p on p.PersonId = pa.PersonId

But how in the world do I get from that shape to my nice grid? I'm pretty sure I did this all the time, or had a helper SP, back in the day... but I'm lost. Help?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-03 : 04:57:58

try this


Declare @StrCols Varchar(max),
	@Str Varchar(max)
		
Select	@StrCols = '',
	@Str = ''
		
Select @StrCols = @StrCols + ', Min(Case When PersonId = ' + Cast(PersonId As Varchar(10)) + '
		and A.AttributeId = pa.AttributeId then pa.degree end) AS "' + PersonName + '"'
From people

Select @Str = 'Select A.AttributeName'
			+ @StrCols + '
			From attributes A
			Inner Join people_attributes pa On PA.AttributeId = A.AttributeId
			Group By A.AttributeName'

Exec (@Str)