How to transpose reccords in SQl Server 2005

Question

Post reply

How to transpose reccords in SQl Server 2005

souravmoysau

Valued Member

Points: 60
More actions
July 27, 2011 at 5:46 am

#390060

Hello,

i am new to this forum,

I want to transpose the records in sql server 2005 sp.

I have try to use Pivot ,but pivot expect aggregate column,but i don't have that. I have attached the sample data out put and expected out put as Excel.Please find it as attachment.

Please guide .

Thanks and regards

Sourav

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76776 More actions · Answer 1

drew.allen

SSC Guru

Points: 76776

July 27, 2011 at 7:16 am

#1359216

In most cases where there is no obvious aggregation, you'll want to use Min() or Max(). If there is only one value corresponding to each pivot value, they'll give you the same result.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChazMan SSCrazy Points: 2259 More actions · Answer 2

Actually, PIVOT is what you are looking for:



select Pivoted.* from

(select c.teacher, c.period, c.classname from dbo.class c) p

pivot

(max(classname) for

Period in ([1st], [2nd], [3rd], [4th], [5th], [6th], [7th], [8th])) as Pivoted

where teacher is not null

where dbo.class is based on your structure results in the following output:

teacher 1st 2nd 3rd 4th 5th 6th 7th 8th

Teacher 1 Class 1 Class 2 Class 3 NULL Class 5 NULL Class 7 NULL

Teacher 2 Class 1 NULL NULL NULL NULL NULL NULL NULL

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

*Don't kick yourself too hard, but the answer is simple if you step back and take another look at it.

Use PIVOT and do a MAX on the ClassDtls column. This works under the ASSUMPTION that a single EmpID can not have more than one ClassDtl for any given Period.



declare @sample table

    (EmpID INT

    ,ClassDtls VARCHAR(25)

    ,Teacher VARCHAR(25)

    ,Period INT)

insert @sample

    values (1,'VIII-A(Eng)','A. GUPTA',1)

          ,(1,'XII-ARTS(MATH)','A. GUPTA',2)

          ,(1,'XII-COMMERCE(BEBM)','A. GUPTA',3)

          ,(1,'VII-B(Eng)','A. GUPTA',5)

          ,(1,'XI-ARTS(MATH)','A. GUPTA',7)

          ,(6,'V-A(Beng)','R. Mondal',1)

          ,(6,'Other','R. Mondal',5)

select

    EmpID

    ,Teacher

    ,ISNULL([1],'') as [1]

    ,ISNULL([2],'') as [2]

    ,ISNULL([3],'') as [3]

    ,ISNULL([4],'') as [4]

    ,ISNULL([5],'') as [5]

    ,ISNULL([6],'') as [6]

    ,ISNULL([7],'') as [7]

from

    (select

        EmpID

        ,ClassDtls

        ,Teacher

        ,Period

     from

        @sample) as p

    pivot

    (MAX(ClassDtls) for period in ([1],[2],[3],[4],[5],[6],[7])) as pvt