July 27, 2011 at 5:46 am
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
July 27, 2011 at 7:16 am
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
July 27, 2011 at 7:19 am
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:
teacher1st2nd3rd4th5th6th7th8th
Teacher 1Class 1Class 2Class 3NULLClass 5NULLClass 7NULL
Teacher 2Class 1NULLNULLNULLNULLNULLNULLNULL
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
July 27, 2011 at 7:22 am
*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
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 27, 2011 at 8:11 am
hi,
Thanks a lot for your help. it works..
July 27, 2011 at 8:12 am
hello ,
thanks all for your value able time to help my problem.
It worked. thanks again for yours value advise.
Sourav
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply