Pivot Concepts - 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)

Pivot Concepts

Author

Topic

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 03:21:42

Kindly, explain the concepts of PIVOTing and PARTITIONing in T-SQL with simple examples..

Balaji.K

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-01 : 03:41:33

http://technet.microsoft.com/en-us/library/ms177410.aspx

-------------------------
R...

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 07:30:39

quote:
Originally posted by rajdaksha

http://technet.microsoft.com/en-us/library/ms177410.aspx

-------------------------
R...

It was a bit useful..
Do you know someother site having SIMPLE examples??
IF yes, let me know..
and explanation for partitioning..

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:34:24

For pivot
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-01 : 13:29:46

EDIT: Note this is not Dynamic, but that concept is certainly not a 'Simple' example

Create TABLE #scores(Grp Nvarchar(1), ID TINYINT,Score TINYINT )
INSERT #scores
SELECT 'A', 1, 70 UNION ALL
SELECT 'A', 2, 77 UNION ALL
SELECT 'A', 7, 85 UNION ALL
SELECT 'B', 10, 95 UNION ALL
SELECT 'B', 11, 85 UNION ALL
SELECT 'C', 15, 77 UNION ALL
SELECT 'B', 18, 55 UNION ALL
SELECT 'B', 26, 98 UNION ALL
SELECT 'A', 90, 91

select grp, AVG(score) from #scores group by grp

SELECT
p.*
FROM
(
SELECT
Grp,
'Avg_Score' as name,
Score
FROM #scores
) AS s
PIVOT
(
AVG(s.Score)
FOR s.grp IN ([A], [B], [C])
) AS p

---------------------------------------------
(note you will have to drop the first table is you plan to execute this entire bit...or just go from the select grp part down...)

To see a partition of the same data set...

Create TABLE #scores(Grp Nvarchar(1), ID TINYINT,Score TINYINT )
INSERT #scores
SELECT 'A', 1, 70 UNION ALL
SELECT 'A', 2, 77 UNION ALL
SELECT 'A', 7, 85 UNION ALL
SELECT 'B', 10, 95 UNION ALL
SELECT 'B', 11, 85 UNION ALL
SELECT 'C', 15, 77 UNION ALL
SELECT 'B', 18, 55 UNION ALL
SELECT 'B', 26, 98 UNION ALL
SELECT 'A', 90, 91

Select Grp,
ID,
Score,
ROW_NUMBER() OVER (PARTITION by Grp Order by Score desc) as Row_Num,
RANK() OVER (PARTITION by Grp Order by Score desc) as Rank,
DENSE_RANK() OVER (PARTITION by Grp Order by Score desc) as Dense_Rank
From #scores

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources