Transposing & Composing Before Closing

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

SQL Server 2000 Forums

Transact-SQL (2000)

Transposing & Composing Before Closing

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 15:36:36

Me again.......I have four payors: BELCH, PREEN, SNORT & SNEEZ (sounds like lawyers, huh?). I have the data by PAYOR, I need it transposed so that it goes by Month and each payor is a column. Here's my sample code:

DROP TABLE tab_XPLIST
DROP TABLE PAYMENTS

CREATE TABLE PAYMENTS
(
SEQ int NULL,
PAYOR varchar (5) NULL,
P_MO varchar (2) NULL,
PMTS int NULL
)
GO

INSERT INTO PAYMENTS VALUES('01','BELCH','01','99')
INSERT INTO PAYMENTS VALUES('01','BELCH','02','91')
INSERT INTO PAYMENTS VALUES('01','BELCH','03','49')
INSERT INTO PAYMENTS VALUES('01','BELCH','04','59')
INSERT INTO PAYMENTS VALUES('01','BELCH','05','08')
INSERT INTO PAYMENTS VALUES('02','PREEN','02','88')
INSERT INTO PAYMENTS VALUES('02','PREEN','03','83')
INSERT INTO PAYMENTS VALUES('02','PREEN','04','83')
INSERT INTO PAYMENTS VALUES('02','PREEN','05','87')
INSERT INTO PAYMENTS VALUES('03','SNORT','01','87')
INSERT INTO PAYMENTS VALUES('03','SNORT','02','80')
INSERT INTO PAYMENTS VALUES('03','SNORT','03','80')
INSERT INTO PAYMENTS VALUES('03','SNORT','04','77')
INSERT INTO PAYMENTS VALUES('05','SNEEZ','01','66')
INSERT INTO PAYMENTS VALUES('05','SNEEZ','02','17')
GO

SELECT SEQ, PAYOR, P_MO, PMTS
INTO tab_XPLIST
FROM PAYMENTS
GO

SELECT * FROM tab_XPLIST GO

This gives me this:

SEQ PAYOR P_MO PMTS
----------- ----- ---- -----------
1 BELCH 01 99
1 BELCH 02 91
1 BELCH 03 49
1 BELCH 04 59
1 BELCH 05 8
2 PREEN 02 88
2 PREEN 03 83
2 PREEN 04 83
2 PREEN 05 87
3 SNORT 01 87
3 SNORT 02 80
3 SNORT 03 80
3 SNORT 04 77
5 SNEEZ 01 66
5 SNEEZ 02 17

(15 row(s) affected)

I want this:

P_MO BELCH PREEN SNORT SNEEZ
----- ----- ----- ----- -----
01 99 __ 87 66
02 91 88 80 17
03 49 83 80 __
04 59 83 77 __
05 8 87 __ __

Is there an easy way to do this?

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-19 : 16:11:33

It's a standard cross-tab query; use CASE to "spread out" the values in the columns, then use GROUP BY to aggregate them into 1 row per month:
[code]
select P_Mo,
sum(case when PAYOR = 'BELCH' then PMTS else 0 end) as Belch,
sum(case when PAYOR = 'PREEN' then PMTS else 0 end) as Preen,
sum(case when PAYOR = 'SNORT' then PMTS else 0 end) as Snort,
sum(case when PAYOR = 'SNEEZ' then PMTS else 0 end) as Sneez
from
PAYMENTS
group by
p_mo
[code]

Format the 0's as "--" at the client or on the report. If you are using a report writer such as Crystal, Reporting services, Impromptu, etc, most of them do cross-tabbing for you.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 16:13:11

Thank you Doctor Cross Join! You get a beer!

But what IF I didn't know who all the payors were? Is there a way to covert to columns generically?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-19 : 16:17:50

I could see that one coming a mile away :) search articles for dynamic cross tab

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-19 : 16:22:52

everything you ever wanted to know about CrossTabs but were afraid to ask:

http://weblogs.sqlteam.com/jeffs/category/156.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 16:31:38

quote:
Originally posted by TG

I could see that one coming a mile away :) search articles for dynamic cross tab

Be One with the Optimizer
TG

That obvious, huh?

But, surely, there's something simple out there....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 16:31:54

I'm not sure that everyone would know that this is called a crosstab query, so I'm not sure that they would know how to search on it.

Jeff, what happened to your weblog about Paladin?

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 16:39:43

Isn't Paladin a mythical character?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 17:18:23

quote:
Originally posted by tduggan

I'm not sure that everyone would know that this is called a crosstab query, so I'm not sure that they would know how to search on it.

Jeff, what happened to your weblog about Paladin?

Tara

Tara......who's Paladin?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 17:20:54

Let's see what Jeff says prior to responding to who Paladin is.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-19 : 17:52:49

short version of the story:

* the owner of the consulting firm (Chris) finally emailed me back after a second email to them
* he said one of his most trusted guys came up with it on a project earlier this year
* He admits that it is very similiar
* he won't admit that it is exactly the same; he claims it is just a coincidence and they are similiar
* Chris seems like a real nice guy and seemed troubled by the situation
* I felt bad and kind of caved; I took the blog down and he put a link to our site on his site

there's more to it, but that's the short version

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 17:58:54

.....and that has what to do with this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 18:15:53

quote:
Originally posted by Xerxes

.....and that has what to do with this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

It has to do with dynamic crosstabs.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 18:28:17

OK.....Tara......I'll play.........

I'm bait; I'll bite: [fish]

*-------*****-----
-*----**-----**---
---***---------***
-*----**-----**---
*-------*****-----

What about dynamic cross tabs does this mean? I know you all think I'm smart

, but honest, I just don't read minds that well. Couldja like clue me in?

. Please?

Thanks....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 18:31:23

Jeff had a weblog about it and now he doesn't. See Jeff's post for why it's not there anymore.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 18:32:59

Yeah, I read his post. I'm still lost...............

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 18:57:30

I've also considered that if I can do a count of whatever elements there may be in The PAYORS---presuming I have no idea how many there could be (like this):

SELECT IDENTITY(INT,1,1) AS i, PAYOR, COUNT(PAYOR) AS Cnt
INTO tab_PAYORS
FROM PAYMENTS
GROUP BY PAYOR
GO

Then there's be a way I could apply that listing as new columns...maybe in a SPROC?

from something like Dr. XJoin suggested:

[code]
CREATE PROC px_COLMKR
@payor varchar (5),

AS
select P_Mo,
sum(case when PAYOR = @payor then PMTS else 0 end) as @payor
FROM PAYMENTS
group by p_mo
return
[code]

but that only does 1 at a time......

am I on the right track anyone?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-09-19 : 21:02:13

quote:
Originally posted by jsmith8858

* he won't admit that it is exactly the same; he claims it is just a coincidence and they are similiar

That's bullshit. They are exactly the same except for the variable names.

Xerxes, try to keep up

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-20 : 14:14:15

OK....

now that 'bullsh

t' has been announced, there must be a cathartic resolution, right?

Can anyone tell me if I was on the right track in the previous post?

Pretty please?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-20 : 14:21:21

Do you really need a cross tab columned result?

You know what a pivot table is? sometimes it is easier to do it that way...

for example...


	col 1	col 2	col 3
row A 	abc	123	xyz
row B 	bcd	234	wxy
row C 	cde	345	vwx

is equivalent to (if read correctly):


row A, 1, abc
row A, 2, 123
row A, 3, xyz
row B, 1, bcd
row B, 2, 234
row B, 3, wxy
row C, 1, cde
row C, 2, 345
row C, 3, vwx

This second query result is MUCH simpler to arrive at.. especially if the number of columns is unknown

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-20 : 14:22:14

doesn't Dr.'s sproc from his blog produce the desired result?
doing 1 at a time is not good

Go with the flow & have fun! Else fight the flow

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources