pivot without aggregate function

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)

pivot without aggregate function

Author

Topic

photoz
Starting Member

4 Posts

Posted - 2007-01-29 : 12:00:51

newie here, I'm looking for a way to do the following:

I need to pivot a vertical table w/o using a aggregate function. Any suggestions you could offer would be very much appreciated.

current table:

name item value
john 001 test
bill 001 123
john 002 <null>
bill 002 789

need to pivot this to:

name 001 002
john test <null>
bill 123 789

Any ideas would be helpful!
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-29 : 12:10:28

Why is not using an aggregate function a requirement?

Is this a homework or job interview question?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 12:30:54

[code]-- prepare sample data
declare @table1 table (name varchar(10), item varchar(10), value varchar(10))

insert @table1
select 'john', '001', 'test' union all
select 'bill', '001', '123' union all
select 'john', '002', null union all
select 'bill', '002', '789'

-- Test this 1
select distinct name,
(select value from @table1 as t1 where t1.name = t.name and t1.item = '001'),
(select t1.value from @table1 as t1 where t1.name = t.name and t1.item = '002')
from @table1 as t

-- Test this 2
select distinct t.name,
l1.value,
l2.value
from @table1 as t
left join (
select name,
value
from @table1
where item = '001'
) as l1 on l1.name = t.name
left join (
select name,
value
from @table1
where item = '002'
) as l2 on l2.name = t.name

-- Test this 3
select name,
max(case when item = '001' then value end),
max(case when item = '002' then value end)
from @table1
group by name[/code]

Peter Larsson
Helsingborg, Sweden

photoz
Starting Member

4 Posts

Posted - 2007-01-29 : 13:00:19

quote:
Originally posted by Michael Valentine Jones

Why is not using an aggregate function a requirement?

Is this a homework or job interview question?

Haha nice try, this is actually for work. I don't care about aggregating the values, they are string anyways, with blank and null as possible values. I simplified the example so as not to confuse.

I am storing survey results into a vertical table and just need a way to pivot them with dynamic column names for the itemid.

There are several hundred items, I don't want to hardcode these, so dynamic sql will be in order.

I initially wanted to use the pivot command, but alas, aggregate func is required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 13:03:40

Why are you so afraid of aggregation? If there is only one value for each row/column, aggregation is still faster than any other technique!

Also see this
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

photoz
Starting Member

4 Posts

Posted - 2007-01-29 : 13:11:45

quote:
Originally posted by Peso

Why are you so afraid of aggregation? If there is only one value for each row/column, aggregation is still faster than any other technique!

Also see this
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

I'm not afraid of it at all, but I don't think it will work with my data since I have to deal with blanks and nulls.

I started off using the following code from here:
http://omnibuzz-sql.blogspot.com/2006/07/pivot-query-generator-for-sql-server.html

When I try to execute this, I get int conversion errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 13:35:52

NULLs and empty string are convertable. Good luck!

You don't listen to suggestions so you will have to do without me on this. My gain.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-29 : 13:41:19

OK, so when you said you needed to do it without an aggregate function you didn't mean it?

CODO ERGO SUM

photoz
Starting Member

4 Posts

Posted - 2007-01-29 : 15:26:54

I'm sorry if I offended anyone... was not the intention.

@Peso:
I did read your article, thank you for providing the link.

@Micheal:
Regarding the aggregate func requirement, I was under the impression I could not use it at all, but now know otherwise.

I ended up using the solution found at this link:

http://omnibuzz-sql.blogspot.com/2006/07/pivot-query-generator-for-sql-server.html

There was a bug in it if you attempted to use a pivot column that was an int, so I fixed that.

xr280xr
Starting Member

2 Posts

Posted - 2011-03-24 : 21:41:57

What pissy forum members you people are. Glad I don't ask questions here...try your question here: http://social.msdn.microsoft.com/Forums/en/transactsql/threads

You'll get constructive answers.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-24 : 22:01:53

I would say dredging up a 4 year old post, just to be disagreeable is "pissy"

Especially for your first post. Nice way to make an introduction.

If you have something constructive to add to the forums, by all means, post. Otherwise, best to say nothing at all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-25 : 03:55:33

WhiteFang is back?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources