UNPIVOT and PIVOT –An Example


declare @t table (id int, pulse1 int, pulse2 int, pulse3 int, pulse4 int, pulse5 int)

insert

into @t values (1 , 76, 89 , null , null , null)

insert into @t values (2 , 89 , 0, 99, 100, null)

insert

into @t values (3 , 0 , 0 , 0 , 110, null)

insert

into @t values (4 , 0 , 0 , 0, 0 , 130)

insert

into @t values (5 , 0 , 0 , 0 , 110, 0)

insert

into @t values (6 , 78, 0 , 0 , 109 , 0)

;

with mycte

AS

(select id, myValue, myPulse,

ROW_NUMBER

() OVER (PARTITION BY id ORDER BY myPulse DESC) rn FROM (select id,

[pulse1]

,[pulse2],[pulse3],[pulse4],[pulse5] from @t)

src

UNPIVOT

(

myValue For myPulse IN ([pulse1],[pulse2],[pulse3],[pulse4],[pulse5])) unpvt

)

SELECT

id, [pulse1],[pulse2],[pulse3],[pulse4],[pulse5]

FROm

(select id, myValue, myPulse FROM mycte

WHERE

ID IN (select id FROM mycte

WHERE

rn=1 AND myValue<>0)) src

PIVOT

(MAX(myValue) FOR

myPulse

IN ([pulse1],[pulse2],[pulse3],[pulse4],[pulse5])) pvt

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