Need help with Transpose

Question

Post reply

Need help with Transpose

BabySteps

Old Hand

Points: 377
More actions
May 12, 2010 at 6:51 am

#97040

I have a query that returns me Locker Numbers with their combinations. The problem is the combination is listed vertically in columns underneath the locker Number. What client needs is the 5 combinations go out horizontally from the locker number. is that possile:
select distinct
LockerNumber
,lstat.LockerStatus
, isnull(loc.Description, ' ') as Description
,loc.CombinationSeries
,lc.Series
,lc.Combination
from dbo.tblLocker loc
left join dbo.tblLockerStatus lstat
on loc.lockerstatusid = lstat.lockerstatusid
left join dbo.tblLockerCombination lc
on loc.lockerid = lc.LockerId
inner join tblschool sch
on loc.schoolid = sch.schoolid
where sch.code = 'ABCD'
and loc.SchoolYearId = '00000000-0000-0000-0000-000000031129'
order by lockernumber, series
set nocount on
The result looks like this
LockerNumberLockerStatusLDescriptionCombinationSeriesseriescombination
1Active 2120-08-22
1Active 2213-01-15
1Active 2308-46-10
1Active 2402-40-04
1Active 2544-32-46
Wanted result
LockerNumber<other columns>combination1 combination2 combination3 combination4 combination5
1<other col values> 20-08-22 13-01-15 08-46-10 02-40-04 44-32-46
Any help is highly appreciated.

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

I suspect that this is a homework question.

Investigate pivot.

Clear Sky SQL
My Blog[/url]

BabySteps Old Hand Points: 377 More actions · Answer 2

from what I undersatnd I need to use and aggregate function when using PIVOT. Is that correct?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Exactly.. use PIVOT with some aggregate functions, then u can transpose rows to columns! There is also another old method, which is bit faster than PIVOT for larger data sets!

But as Dave said, this request looks like a homework/assignment. Have you worked on this, if yes, can u post what query you worked on, to solve the problem!!

Cheers!

BabySteps Old Hand Points: 377 More actions · Answer 4

It is not a hw assignment..I am very new to development and this is my first post on any forum ever. So I might not know how to pose the question right..

Here is what I tried

create table #lockerTry

(

LockerNumber varchar(10)

,LockerStatus varchar(50)

, Description varchar(50)

,CombinationSeries tinyint

,Series tinyint

,Combination varchar(16)

)

go

insert into #lockerTry

select distinct

LockerNumber

,lstat.LockerStatus

, isnull(loc.Description, ' ')

,loc.CombinationSeries

,lc.Series

,lc.Combination

from dbo.tblLocker loc

left join dbo.tblLockerStatus lstat

on loc.lockerstatusid = lstat.lockerstatusid

left join dbo.tblLockerCombination lc

on loc.lockerid = lc.LockerId

inner join tblschool sch

on loc.schoolid = sch.schoolid

where sch.code = 'ABCD'

and loc.SchoolYearId = '00000000-0000-0000-0000-000000031129'

--order by lockernumber, series

go

select * from #lockerTry

SELECT LockerNumber

, ISNULL(Combination1, 0) Combination1

, ISNULL(Combination2, 0) Combination2

, ISNULL(Combination3, 0) Combination3

, ISNULL(Combination4, 0) Combination4

, ISNULL(Combination5, 0) Combination5

FROM

(

SELECT LockerNumber

, Combination

, 'Combination'

+ CAST(ROW_NUMBER() OVER (PARTITION BY LockerNumber ORDER BY LockerNumber) AS VARCHAR(5)) AS col

FROM #lockerTry) AS sourcetable

PIVOT

( AVG(Combination)

FOR col IN ([Combination1],[Combination2],[Combination3],[Combination4],[Combination5])

) AS pivottable;

go

BabySteps Old Hand Points: 377 More actions · Answer 5

Ok I think I got it..Thanks for looking into it.