Dynamic Pivot Sample



Create table yourtable (itemID INT, part CHAR(1))

INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N'SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols
FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt'

exec sp_executesql @sql;

drop table yourtable

 

 

 

--Another

create table test (ADMISSIONNO int,FULLNAME varchar(50), SUBJECT varchar(50), GRADE Char(1))
Insert into test values

(3886,'ALIYU USMAN','MATHEMATICS','A'),
(3886,'ALIYU USMAN','ENGLISH','B'),
(3886,'ALIYU USMAN','BIOLOGY','B'),

(3887,'JOHN INNOCENT','MATHEMATICS','A'),
(3887,'JOHN INNOCENT','ENGLISH','A'),
(3887,'JOHN INNOCENT','BIOLOGY','A')

---Case
Select ADMISSIONNO
,Max(Case WHEN Subject='MATHEMATICS' Then Grade End) MATHEMATICS
,Max(Case WHEN Subject='ENGLISH' Then Grade End) ENGLISH
,Max(Case WHEN Subject='BIOLOGY' Then Grade End) BIOLOGY
from test
Group by ADMISSIONNO

--Pivot
Select ADMISSIONNO,MATHEMATICS, ENGLISH , BIOLOGY
from test
Pivot (Max(Grade) For SubJect IN ([MATHEMATICS],[ENGLISH],[BIOLOGY])) pvt

--Dynamic Pivot

DECLARE @cols AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

SELECT @cols = STUFF((select DISTINCT ', ' + quotename(SubJect ,']')
FROM test
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @cols

Set @sql=N'SELECT ADMISSIONNO, '+ @cols + ' FROM test PIVOT (Max(Grade) for Subject IN ('+ @cols +')) pvt'

exec sp_executesql @sql;
drop table test

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s