Get first n records and Pivot - 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)

Get first n records and Pivot

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2008-09-12 : 11:11:40

Here is my Question

-- Sample Data :


Select * into #t from
(Select 3 as id,'C' as class
Union Select 3 ,'P' 
Union Select 3 ,'R' 
Union Select 3 ,'A' 
Union Select 3 ,'X' 
Union Select 3 ,'Y' 
Union Select 3 ,'Q' 
Union Select 3 ,'B' 
Union Select 3 ,'W' 
Union Select 5 ,'V' 
Union Select 5 ,'N' 
Union Select 5 ,'M' 
Union Select 5 ,'E' 
Union Select 5 ,'Z' 
Union Select 5 ,'L' 
Union Select 5 ,'F' 
Union Select 5 ,'G' 
Union Select 5 ,'W' 
Union Select 7 ,'H' 
Union Select 7 ,'A' 
Union Select 4 ,'F' 
Union Select 4 ,'G' 
Union Select 4 ,'W' ) Q

-- I use the following method to get the top 6 records for each ID :


SELECT		a.id, a.class
FROM		#t a
INNER JOIN	#t b ON b.class <= a.class AND b.id = a.id
GROUP BY	a.id,	a.class
HAVING		COUNT(DISTINCT b.class) <= 6
ORDER BY	a.id

-- I need to get the first 6 'Class' values for each 'ID'. Then I need to transpose the table as follows :


Col0	Col1	Col2	Col3	Col4	Col5	Col6
----	----	----	----	----	----	----
3	A	B	C	P	Q	R
4	F	G	W	Null	Null	Null
5	E	F	G	L	M	N
7	A	H	Null	Null	Null	Null

Srinika

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-12 : 17:59:46

[code]
drop table #t
Select * into #t from
(Select 3 as id,'C' as class
Union Select 3 ,'P'
Union Select 3 ,'R'
Union Select 3 ,'A'
Union Select 3 ,'X'
Union Select 3 ,'Y'
Union Select 3 ,'Q'
Union Select 3 ,'B'
Union Select 3 ,'W'
Union Select 5 ,'V'
Union Select 5 ,'N'
Union Select 5 ,'M'
Union Select 5 ,'E'
Union Select 5 ,'Z'
Union Select 5 ,'L'
Union Select 5 ,'F'
Union Select 5 ,'G'
Union Select 5 ,'W'
Union Select 7 ,'H'
Union Select 7 ,'A'
Union Select 4 ,'F'
Union Select 4 ,'G'
Union Select 4 ,'W' ) Q

select
Id
,[1] as Col1
,[2] as Col2
,[3] as Col3
,[4] as Col4
,[5] as Col5
,[6] as Col6
from
(
SELECT
a.id
, a.class
, Row_Number() over (Partition by a.ID order by a.class) as RowID
FROM
#t a
) aa
Pivot
(
Max(Class)
for
aa.ROWID in ([1],[2],[3],[4],[5],[6])) p

--or

select
Id
,[1] as Col1
,[2] as Col2
,[3] as Col3
,[4] as Col4
,[5] as Col5
,[6] as Col6
from
(
SELECT a.id, a.class, Row_Number() over (Partition by a.ID order by a.class) as RowID
FROM #t a
INNER JOIN #t b ON b.class <= a.class AND b.id = a.id
GROUP BY a.id, a.class
HAVING COUNT(DISTINCT b.class) <= 6
) aa
Pivot
(
Max(Class)
for
aa.ROWID in ([1],[2],[3],[4],[5],[6])) p
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-12 : 18:08:58

This is the query factoring in possible duplicates. It also provides better performance using the sample data you provided than the Join query you illustrated.


select
Id
,[1] as Col1
,[2] as Col2
,[3] as Col3
,[4] as Col4
,[5] as Col5
,[6] as Col6
from
(
	select  Row_Number() over (Partition by aa.ID order by aa.class) as RowID,*
	from
	(
		SELECT	distinct	
				a.id
				, a.class
		FROM
			#t a
	) aa
) aaa
Pivot
(
Max(Class)
for 
aaa.ROWID in ([1],[2],[3],[4],[5],[6])
) p

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-15 : 03:51:55

If the values are dynamic, try
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources