2

In SQL server 2008, I have below table.

enter image description here

I do not how to use Pivot without giving for value in ([val1],[val2],[val2],..)

Any hep in this regard will be greatly appreciated.

Thanks.

create table [VJ1].[dbo].[pivot] (class varchar(25) null, name varchar(25) null)

insert into [VJ1].[dbo].[pivot] (class,name) 
values ('class1','Peter'),
       ('class1','John'),
       ('class1','Marry'),
       ('class1','Ana'),
       ('class1','Julie'),
       ('class1','Lydia'),           
       ('class2','Ryan'),
       ('class2','Aaron'),           
       ('class2','Jacques'),
       ('class2','Jaanu'),           
       ('class3','Nita'),
       ('class3','Nina'),
       ('class3','Lili'),           
       ('class3','Rose'),
       ('class3','Jack'),
       ('class3','Tom')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user219628
  • 3,755
  • 8
  • 35
  • 37
  • See my answer here to a very similar question. http://stackoverflow.com/questions/5554391/sql-query-problem/5554861#5554861 – Martin Smith Apr 06 '11 at 21:29
  • Note that the insert code is not the same shown in the image. ('class3','Tom') is in the code but not in the image. – pcofre Apr 07 '11 at 11:34

1 Answers1

1

You don´t need to know the values, just the maxium number of names associated with a class.

;WITH PvtCte as
(
select  Class,Name,RANK() over (partition by Class order by Name) r
from    [pivot]
)
SELECT class,[1],[2],[3],[4],[5],[6]
FROM 
(SELECT Class,Name,r
FROM PvtCte) p
PIVOT
(
MIN(Name)
FOR r IN
( [1], [2], [3], [4], [5],[6] )
) AS pvt

If you aldo don´t know the maxium number of names associated with a class you can try building the same query above dinamicaly. Hope it helps!

pcofre
  • 3,976
  • 18
  • 27