PIVOT query

Question

Post reply

PIVOT query

Ed Zann

SSCrazy Eights

Points: 8694
More actions
March 5, 2009 at 2:38 pm

#128247

I have not used the new PIVOT operator and from BOL I'm not clear if or how the following would be accomplished:
Let's say we can have 1 to 4 owners of a checking account:
Acct# CusName
1234 Joe Smith
1234 Mary Smith
1234 Nancy Smith
1234 Larry Smith
5678 Moe Doe
5678 Chloe Doe
I want to Pivot as
Acct# Owner1 Owner2 Owner3 Owner4
Is this possible using the PIVOT operator?

Viewing 15 posts - 1 through 15 (of 15 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Does this helps:

declare @t table(acc int, CustName varchar(100))

insert into @t (acc, CustName) VALUES (1234, 'Joe Smith')

insert into @t (acc, CustName) VALUES (1234, 'Mary Smith')

insert into @t (acc, CustName) VALUES (1234, 'Nancy Smith')

insert into @t (acc, CustName) VALUES (1234, 'Larry Smith')

insert into @t (acc, CustName) VALUES (5678, 'Moe Doe')

insert into @t (acc, CustName) VALUES (5678, 'Chloe Doe')

select acc, Owner1, Owner2, Owner3, Owner4

from

(

select *,

'Owner'+cast(row_number()over(partition by acc order by CustName)as char(1)) rn

from @t

)src

PIVOT (min(CustName) FOR rn in ( Owner1, Owner2, Owner3, Owner4 )) as p

order by acc

* Noel

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I've gotta ask, Ed... why do you need this type of denormalize output? What is that output going to be used on? I'm asking because I like to know so I might be able to help others figure out what they want to do and also because, depnding on your answer, there may be better ways to do it.

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I will echo Jeff's question (which I couldn't have phrased better myself).

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

Jeff Moden (3/5/2009)
I've gotta ask, Ed... why do you need this type of denormalize output? What is that output going to be used on? I'm asking because I like to know so I might be able to help others figure out what they want to do and also because, depnding on your answer, there may be better ways to do it.

Jeff,

My task is to identify opportunities to issue combined bank statements where the owners of accounts are the same, although not necessarily in the same capacity, ie, primary owner on one account may be secondary on another. Some preliminary queries indicate there may be up to 6 owners to be considered. There is a many to many join table that sits between the customer and account tables.

I'm still kicking around how to accomplish this. My current line of thinking is to concatenate the alphanumeric owner id's for each acount in sorted order with a UDF, then use this composite "Key" to identify accounts with the same owners.

I was thinking if I could pivot the data then I would just feed owner1 - owner6 into the UDF to get back my new sorted composite key and carry on from there.

I suppose this could also be accomplished without pivoting the data, though I still need to work that out.

Any suggestions are welcome.

Thanks

Ed

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 5

noeld,

Thank you, I think that your pivot script gives me what I need. I never used the row_number over partition functions available in SQL 2005. Since I can output the owners in sorted order I won't have to resort to a UDF as I was planning.

I don't know if this is the most efficient approach to this problem; others may weigh in on that; but it will get me where I want to go.

Thanks

Ed

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Ed (3/6/2009)
Jeff Moden (3/5/2009)
I've gotta ask, Ed... why do you need this type of denormalize output? What is that output going to be used on? I'm asking because I like to know so I might be able to help others figure out what they want to do and also because, depnding on your answer, there may be better ways to do it.
Jeff,
My task is to identify opportunities to issue combined bank statements where the owners of accounts are the same, although not necessarily in the same capacity, ie, primary owner on one account may be secondary on another. Some preliminary queries indicate there may be up to 6 owners to be considered. There is a many to many join table that sits between the customer and account tables.
I'm still kicking around how to accomplish this. My current line of thinking is to concatenate the alphanumeric owner id's for each acount in sorted order with a UDF, then use this composite "Key" to identify accounts with the same owners.
I was thinking if I could pivot the data then I would just feed owner1 - owner6 into the UDF to get back my new sorted composite key and carry on from there.
I suppose this could also be accomplished without pivoting the data, though I still need to work that out.
Any suggestions are welcome.
Thanks
Ed

Thank you for the response... that's excellent. Now that I understand what you're trying to do, I can ask some intelligent questions and maybe help a bit....

I see an account # and CusName in your example data... doesn't the account have an association to the customer by an ID or other unique identifier? I'm asking because I think you'll run into some pretty nasty problems if there are, for example, 2 Larry Smith's.

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

I'm assuming you just gave us names of customers instead of their alphanumeric customer ID for simplicity's sake. Since you said you wanted to sort and concatenate those values to produce a sort of group ID, here is the best way I know of to accomplish that in one step.

Obviously you can replace the commas with whatever delimiter you wish.

Jeff may be able to advise you of better ways to accomplish your ultimate goal.

declare @sample table (ID int identity(1,1) primary key, Acct# int, CusName varchar(30))

insert into @sample

select 1234,'Joe Smith' union all

select 1234,'Mary Smith' union all

select 1234,'Nancy Smith' union all

select 1234,'Larry Smith' union all

select 5678,'Moe Doe' union all

select 5678,'Chloe Doe'

SELECT Acct#,stuff((SELECT ',' + cusName

FROM @sample s2

WHERE s2.acct# = s1.acct# -- must match GROUP BY below

ORDER BY cusname

FOR XML PATH('')

),1,1,'') as [Concatenated]

FROM @sample s1

GROUP BY Acct# -- without GROUP BY multiple rows are returned

ORDER by Acct#

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Bob certainly did that right for the problem given... I'm just concerned about using names which are likely not unique... especially if it's for people's bank accounts. Heh... see what happens to your bank if you give my money to someone else with the same name... Like the Dunkin' Donuts grannies used to say, "See you in court, SONNY!" 😛

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

My current line of thinking is to concatenate the alphanumeric owner id's

This is what I was keying on. But your point is well taken, Jeff. As a former attorney, I am very sensitive to the consequences of mistakenly attributing something to someone, or of FAILING to attribute something to someone.

Ed, please let us know if I was reading you wrong.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Bob Hovious (3/6/2009)
As a former attorney...

Heh... now I know where you get that wonderful sense of humor... 😀

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

I usually refer to myself as a "reformed attorney", but some of my friends call me a "recovering attorney." 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 12

Bob Hovious (3/6/2009)
My current line of thinking is to concatenate the alphanumeric owner id's
This is what I was keying on. But your point is well taken, Jeff. As a former attorney, I am very sensitive to the consequences of mistakenly attributing something to someone, or of FAILING to attribute something to someone.
Ed, please let us know if I was reading you wrong.

Let not your heart be troubled....I used names in the example for simplicity sake. Our system uses a unique alphanumeric identifier for customer id. I was able to use the pivot query that noeld supplied to pivot the unique account owner id's in alpha order. Then I could group and join on those fields to work with accounts that had the same ownership.

Using the pivot approach worked quite well and it ran quickly. I had not workthe pivot or row_number features of SQL 2005 before - this was an excellent learning ed with experience for me.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Good deal, Ed 🙂

Quick question, after you do the pivot, are you having to do another step to group the various IDs into a single string, or are you joining to all of the ID columns in the table produced by the pivot?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 14

I used the pivoted ID columns to join on and to group on. I did not have to concatenate the ID's. There was additional analysis to be done, so step 1 was to dump this info out to a table that could be used in further queries. The business unit has not decided exactly what they're after, so we're in the process of fine tuning the results.