Cant seem to get this - 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

General SQL Server Forums

New to SQL Server Programming

Cant seem to get this

Author

Topic

Pomm3
Starting Member

6 Posts

Posted - 2011-10-05 : 04:52:53

Hi Folks,

After a long time away from sql, Im back in it now. I have this data below in a table A and I need to do a pivot on it but Im not sure as how to approach this.

Btw, Im running this on Sqlserver 2000 & hence no analytical functions or anything like that...so, this has to be done the hammer & chisel way.

Table A

MDN PRV_NUM MARKET

3141111111 2132111111 LAXIN
3141111111 4808381111 PHEON
3141111111 5031111111 PORTL

In my report I would like to see as below -

MDN MARKET1 MARKET2 MARKET3
3141111111 2132111111 4808381111 5031111111

Almost forgot to mention that there could be a maximum of four different markets that the MDN can belong in but if you wish to, you can keep it upto only 3 markets as that is the case for the most part.

Please advise.

regards,
PGonzalez

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:03:36

[code]
select MDN,
MAX(CASE WHEN rn=1 THEN PRV_NUM ELSE NULL END) AS MARKET1,
MAX(CASE WHEN rn=2 THEN PRV_NUM ELSE NULL END) AS MARKET2,
MAX(CASE WHEN rn=3 THEN PRV_NUM ELSE NULL END) AS MARKET3,
MAX(CASE WHEN rn=4 THEN PRV_NUM ELSE NULL END) AS MARKET4
from
(
select MDN, PRV_NUM, MARKET,
coalesce((select count(*) from tableA where MDN=a.MDN and PRV_NUM<a.PRV_NUM),0) + 1 AS rn
from tableA a
)m
group by MDN
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pomm3
Starting Member

6 Posts

Posted - 2011-10-05 : 05:28:12

Man 'o man,

How can this even be possible ? You just humbled me man... I looked at your profile and noticed that you are very young to me and yet, what a crisp & correct answer.

May God bless you my friend.

regards,
PGonzalez.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:40:12

no problem...you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pomm3
Starting Member

6 Posts

Posted - 2011-10-14 : 02:46:07

Visakh16,

Till today, this query ran like a charm and the best part of this is that this query ported onto Oracle as well without any issues and bought back the same results during the data validation stage post migration. Now today, things have changed a bit.

First I have to confess that for me to get the data to the following 3 columns
MDN PRV_NUM MARKET
I have to jump from 4 tables and do all kinds of where clauses to arrive at the final data that I presented to you.
Now, for me to implement the query is getting impossible as I was just given this temp table initially and now it is a non-materialized view on the oracle side. So this query is very tedious to plug into the existing framework & it is taking almost 1 minute to get data for only 1 row and this is rough.

Is there any way that you can write it in any other shape or form so that it runs on Sql server 2000 and oracle as well ? I know Im asking too much here but if you can help, I would be quite thankful for it.

regards,
PGonzalez

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 03:58:30

The query i gave will work in sql 2000
I'm not sure on Oracle as you dont have any experience in Oracle. FOr that you might have post it in some Oracle forums as this is MS SQL Server forum and doesnt have too many people with Oracle experience.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pomm3
Starting Member

6 Posts

Posted - 2011-10-14 : 04:38:17

Visakh16,

First off, I admire your brilliance young man.

Yes, I totally get it that this is sql server forum and I certainly understand that.

Now, getting back to the sql server 2000 itself, here is a sample data scenario ...

MDN PRV_NUM MARKET

3141111111 2132111111 LAXIN
3141111111 4808381111 PHEON
3141111111 5031111111 PORTL
2172391122 4083839934 SANJO
2172391122 6309383499 CHIIL
2172391122 2133737322 LAXIN
2172391122 6309383499 CHIIL

Can we get an output as shown below ... is this even possible ? Sorry if Im asking a crazy question in here .....

LAXIN PHEON PORTL SANJO CHIIL
3141111111 1 1 1 0 0
2172391122 1 0 0 0 1

In other words, can we show the counts of occurrences of each individual MDN under a given market.

regards,
PGonzalez

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 04:42:30

possible


SELECT MDN,
COUNT(CASE WHEN MARKET='LAXIN' THEN 1 ELSE NULL END) AS [LAXIN],
COUNT(CASE WHEN MARKET='PHEON' THEN 1 ELSE NULL END) AS [PHEON],
COUNT(CASE WHEN MARKET='PORTL' THEN 1 ELSE NULL END) AS [PORTL],
COUNT(CASE WHEN MARKET='SANJO' THEN 1 ELSE NULL END) AS [SANJO],
COUNT(CASE WHEN MARKET='CHIIL' THEN 1 ELSE NULL END) AS [CHIIL]
FROM Table
GROUP BY MDN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pomm3
Starting Member

6 Posts

Posted - 2011-10-14 : 04:58:46

Hey Mr.Visakh16,

I totally blew it..I was doing too many things at one time. I should have been clear'er.

My question rather was, how do I tabulate it if I dont even know what market Im in ? unless there is an occerence of the MDN number in that market .. I wouldnt even see it...there can be more than 10 markets for certain MDN's spreading all across continental united states and there can be only 1 market for a MDN sometimes as well. So, I cant hard code the value and then do a look up on it it has to be done on the fly .....
I hope Im clear now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 05:15:52

ok. then make it dynamic like below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources