UNPIVOT in T-SQL 2000

Question

UNPIVOT in T-SQL 2000

JMI

SSChampion

Points: 11831
More actions
September 15, 2009 at 1:26 pm

#93950

How do i change this:
DECLARE @Orders TABLE
(CUSIP varchar(15), Spread float, IndexValue float, COB smalldatetime)
-- Load Sample Data
INSERT INTO @Orders VALUES ('548661CH', 153.21, 148.55, '9-9-2009')
INSERT INTO @Orders VALUES ('548661CK', 151.43, 146.17, '9-10-2009')
Results:
CUSIPSpreadIndexValueCOB
548661CH153.21148.552009-09-09 00:00:00
548661CK151.43148.552009-09-10 00:00:00
into this:
CUSIPSpreadCOB
548661CH153.212009-09-09 00:00:00
548661CK151.432009-09-10 00:00:00
[Index] 148.55 2009-09-10 00:00:00

Viewing 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

What I always recommend on pivot/unpivot questions is, use a better tool than T-SQL for it. Excel, Reporting Services, Crystal Reports, can all do pivot/unpivot better than SQL Server can.

If you absolutely MUST use T-SQL for it, there's a good article on http://www.simple-talk.com about dynamic pivots in T-SQL. Search for that and you'll get what you need. There are similar articles on this site. Google has them indexed, last I checked.

But I recommend against it. Use the presentation layer to do pivots. It's easier, more efficient, more flexible, and more user-friendly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JMI SSChampion Points: 11831 More actions · Answer 2

It is for Reporting Services.

I have a Line Chart that is supposed to show

5 lines. 4 CUSIP values and one [IndexValue] value.

With just CUSIP value the chart looks good.

But as soon as I add [IndexValue] into

[Chart / Drop data fields here] section

Legend gets screwed up.

See what I mean below.

So I thought if I UNPIVOT the data and show both [CUSIP] and [IndexValue]

in one column this would fix my problem.

I'd just write 'Index' in CUSIP column when showing [IndexValue] in [Spread] column.

JMI SSChampion Points: 11831 More actions · Answer 3

Resolved !

UNPIVOTing fixed the chart!

here is the code:

select

#cusip.COB

, #cusip.CUSIP

, #cusip.Spread

from

#cusip inner join #CreditSpreadCurve_250 on

#cusip.[Index] = #CreditSpreadCurve_250.Instrument

and #cusip.COB = #CreditSpreadCurve_250.COB

UNION