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
 General SQL Server Forums
 New to SQL Server Programming
 pivot table

Author  Topic 

dilu720
Starting Member

2 Posts

Posted - 2009-04-27 : 01:22:12
Hai all

I have a tablr like the following

Name Age Address
a 10 aaa
b 11 bbb
c 13 hhh

I want to retrive the data from the table as follows

Name a b c
Age 10 11 13
address aaa bbb hhh

help me
thanx and regards
Dilu

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 02:16:28
Hi,

Using Pivot Clause we cant bring the result as you wish.
Go to Top of Page

dilu720
Starting Member

2 Posts

Posted - 2009-04-27 : 02:31:06
Then How will i get it
Go to Top of Page

yonabout
Posting Yak Master

112 Posts

Posted - 2009-04-27 : 13:10:27
Hi,

No idea why you'd want to do this - It would be better if you could supply more info so I can see what you're trying to do and why.

Anyhow, I think this is what aprichard means by the pivot clause - apologies if I'm way off the mark

--Create a table
create table dbo.NAD
(Name varchar(5),
Age integer,
Address varchar(5)
)

--fill it with sample data
insert into dbo.nad
select 'a', 10, 'aaa' union
select 'b', 11, 'bbb' union
select 'c', 12, 'bbb'

--use the pivot thing
select
'Name' as Name,
max(case when name = 'a' then 'a' end) as A,
max(case when name = 'b' then 'b' end) as B,
max(case when name = 'c' then 'c' end) as C

from dbo.nad


Cheers,

Yonabout
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-27 : 23:13:32
try this too

declare @NAD table
(Namevl varchar(5),
Age integer,
Address varchar(5)
)

insert into @NAD
select 'a', 10, 'aaa' union
select 'b', 11, 'bbb' union
select 'c', 12, 'bbb'

select 'age',max([10]),max([11]),max([12])
from @NAD
pivot(max([namevl]) for age in ([10],[11],[12]))p

union all

select 'name',convert(varchar(32),max([10])),convert(varchar(32),max([11])),convert(varchar(32),max([12]))
from @NAD
pivot(max([age]) for age in ([10],[11],[12]))s

union all

select 'address',max([10]),max([11]),max([12])
from @NAD
pivot(max(address) for age in ([10],[11],[12]))k

check this link too
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
Go to Top of Page
   

- Advertisement -