Rows to columns + count

Question

Rows to columns + count

JMI

SSChampion

Points: 11831
More actions
November 19, 2009 at 2:05 pm

#99964

I'm on SQL Server 2000.
Here is my data:
Index CUSIPDataSrc
------------------------------
A 556MHS
A 556MHS
A 556MHS
A 556FRDOps
A 556EJV
A 557MHS
A 557FRDOps
A 557EJV
How do convert it to:
Index CUSIP MHS FRDOps EJV
---------------------------------------------------------
A 556 31 1
A 557 1 1 1
[DataSrc] can have maximum of 4 different values.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could have a look at the following articles referenced in my signature: CrossTab or Dynamic Cross Tab (if DataSrc contains an unknown list of elements).

I assume you're using SQL2000 (since posted in the SS2K forum). Otherwise I would have suggested to look into PIVOT.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

JMI SSChampion Points: 11831 More actions · Answer 2

Dear lmu92,

Thank you for trying to help but ...

your article is very big. I didn't have time to search through it to find if it's relevant to my case.

There is a much better and shorter article at

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

written by Muthusamy Anantha Kumar aka The MAK.

This gave me a good clue.

Thank you Muthusamy Anantha Kumar!

Here is my solution:

drop table #BondSpread

create table #BondSpread

(

[Index] varchar(15),

CUSIP varchar(15),

Spread float,

DataSrc varchar(10)

)

insert #BondSpread ([Index],CUSIP,Spread,DataSrc) values ('UK_GEN_BBB','00998064',21.5447,'MHS')

insert #BondSpread ([Index],CUSIP,Spread,DataSrc) values ('UK_GEN_BBB','00998064',36.5447,'FRDOps')

insert #BondSpread ([Index],CUSIP,Spread,DataSrc) values ('C_IND_A','00998064',21.5447,'EJV')

select * from #BondSpread

/*

IndexCUSIPSpreadDataSrc

-----------------------------------------------------------------------------

UK_GEN_BBB0099806421.5447MHS

UK_GEN_BBB0099806436.5447FRDOps

C_IND_A0099806421.5447EJV

*/

SELECT [Index],CUSIP,

COUNT(CASE DataSrc WHEN 'MHS' then 1 else null end) AS MHS,

COUNT(CASE DataSrc WHEN 'FRDOps' then 1 else null end) AS FRDOps,

COUNT(CASE DataSrc WHEN 'EJV' then 1 else null end) AS EJV,

COUNT(CASE DataSrc WHEN 'FTG' then 1 else null end) AS FTG

FROM #BondSpread

--where COB >= '11/18/2008' --and [Index] = 'A_BF_A' and CUSIP = 'EH007645'

GROUP BY [Index],CUSIP

/*

IndexCUSIPMHSFRDOpsEJVFTG

---------------------------------------------------------------------------------

C_IND_A 009980640030

UK_GEN_BBB 009980642100

*/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

BWAAA-HAAA!!!! :hehe: Thanks for posting the answer you got but if you had posted data that way in your original post, you'd have had an answer on this forum almost immediately. Just incase you want a quick answer in the future, you should read how to do that... the URL is the first one in my signature below. If you're the same "riga" that I'm thinking of, you said you'd post correctly in the future and you haven't. If you're not the same "riga", then it's time you learned. Read the article I just pointed out.

Also, Lutz wouldn't have referred you to that other article unless he was absolutely sure that it would solve your problem. Now that you have a little time on your hands, you should go back and read it so you can figure this type of stuff out on your own. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)