Pivot Query - 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 2005 Forums

Transact-SQL (2005)

Pivot Query

Author

Topic

netsurfer
Starting Member

5 Posts

Posted - 2009-01-09 : 10:38:52

Hi all, i have a question i have this query:

select ar.articlePerson , a.number ,a.title from articlePerson ar inner join article a on ar.idArticle = a.idArticle
where ar.idarticlePerson = 4

The result is

-----------------------------
4 1 MyArticle1
4 2 MyArticle2
4 3 MyArticle3

Is it anyway to show results like
----------------------------------------------
4 1 MyArticle1 2 MyArticle2 3 MyArticle3

i'm stuk in here at hours :/

Can anyone help me???
thanks in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-09 : 10:56:43

Do a search on the forums for PIVOT or Transpose.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 11:20:07

Are you looking for this?

SELECT id,
(
SELECT convert(varchar(20),number) + Title + ' ' as [text()]
FROM table
ORDER BY Title
FOR XML PATH('')
) AS TitleList
from table
group by Id

or this?

Select id,
max(case when number = 1 and title ='MyArticle1' then convert(varchar(20),number)+' ' +title else Null end)as Title1,
max(case when num = 2 and title ='MyArticle2' then convert(varchar(20),number)+' ' +title else Null end)as Title2,
max(case when num = 3 and title ='MyArticle3' then convert(varchar(20),number)+' ' +title else Null end)as Title3
from table
group by id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:35:26

just in case your number of items are not constant, you need this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-09 : 23:21:53

Are You looking For this, just try it once

declare @temp table ( i int,j int,k varchar(32) )
insert into @temp
select 4, 1, 'MyArticle1' union all
select 4, 2, 'MyArticle2' union all
select 4, 3, 'MyArticle3'

select distinct i,
stuff(( select distinct ' '+ cast(j as varchar(32))+' '+k from @temp t where t.i = t1.i for xml path('')),1,1,'') from @temp t1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 23:40:05

select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from art as p
pivot
(max(title) for title in ([MyArticle1],[MyArticle2],[MyArticle3])) as pvt
group by id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:42:37

quote:
Originally posted by Nageswar9

Are You looking For this, just try it once

declare @temp table ( i int,j int,k varchar(32) )
insert into @temp
select 4, 1, 'MyArticle1' union all
select 4, 2, 'MyArticle2' union all
select 4, 3, 'MyArticle3'

select distinct i,
stuff(( select distinct ' '+ cast(j as varchar(32))+' '+k from @temp t where t.i = t1.i for xml path('')),1,1,'') from @temp t1

this will give you space delimited list of all values in single column. i think what OP wants is values on seperate columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:46:19

quote:
Originally posted by bklr

select *
from
(
select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from art
)as p
pivot
(max(title) for title in ~~([MyArticle1],[MyArticle2],[MyArticle3])~~([value1],[value2],[value3]))
as pvt
~~group by id~~

probably you meant this

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-10 : 02:50:22

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

select *
from
(
select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from art
)as p
pivot
(max(title) for title in ~~([MyArticle1],[MyArticle2],[MyArticle3])~~([value1],[value2],[value3]))
as pvt
~~group by id~~

probably you meant this

how will u get the output if u keep value1,value2,value3 in title columns

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-10 : 02:53:02

hi visakh,
just check once both queries

declare @temp table ( id int,number int,title varchar(32) )
insert into @temp
select 4, 1, 'MyArticle1' union all
select 4, 2, 'MyArticle2' union all
select 4, 3, 'MyArticle3'

select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from @temp as p
pivot
(max(title) for title in ([MyArticle1],[MyArticle2],[MyArticle3])) as pvt
group by id

select *
from
(
select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from art
)as p
pivot
(max(title) for title in ([value1],[value2],[value3]))
as pvt
group by id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:55:07

quote:
Originally posted by bklr

hi visakh,
just check once both queries

declare @temp table ( id int,number int,title varchar(32) )
insert into @temp
select 4, 1, 'MyArticle1' union all
select 4, 2, 'MyArticle2' union all
select 4, 3, 'MyArticle3'

select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from @temp as p
pivot
(max(title) for title in ([MyArticle1],[MyArticle2],[MyArticle3])) as pvt
group by id

select *
from
(
select id,max(convert(varchar(32),number)+' '+ [MyArticle1]) as value1,max(convert(varchar(32),number)+' '+ [MyArticle2]) as value2,max(convert(varchar(32),number)+' '+ [MyArticle3]) as value3
from art
)as p
pivot
(max(title) for title in ([value1],[value2],[value3]))
as pvt
group by id

oh..ok..sorry i misunderstood.
you're right

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources