Selecting row data into columns

Question

Selecting row data into columns

rfranklin-741429

Old Hand

Points: 348
More actions
May 11, 2010 at 2:29 pm

#140857

I'm trying to take some relational data and export it into an Excel Spreadsheet but I'm having issues getting the child data from rows into columns.
Example:
I have a table called accounts that links to csi_acct_leadsource joining on accountid.
select * from accounts a
inner join csi_acct_leadsource c
on a.accountid = c.accountid
I need to take the 1:many lead sources per account and return those as columns in my query so it will look like account, leadsource1, leadsource2, leadsource4, etc. I will also need to return the sourcedate for each as well.
It's currently stored in the table like this:
csi_acct_leadsource
accountid
leadsourceid
leadsourcedate
The description of the leadsource is actually stored in another table called leadsource that looks like
leadsourceid
leadsourcedesc
So I have to also join to leadsource to get the actual description that will be used in the column leadsource1, leadsource2, etc
It would be something like this:
select a.account, c.leaddate, l.description
from sysdba.account a
inner join sysdba.csi_acct_leadsource c
on a.accountid = c.accountid
inner join sysdba.leadsource l
on l.leadsourceid = c.leadsourceid
order by account
I need to figure out a way to take all these detail records and display them as columns. I've tried self joins and it only really works for the first record.
Any helpful hints would be greatly appreciated.
Thanks!
Rebecca

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Rebecca
I need to take the 1:many lead sources per account and return those as columns in my query so it will look like account, leadsource1, leadsource2, leadsource4, etc.

I suggest you read this article on SSC by Jeff Moden:

[http://www.sqlservercentral.com/articles/T-SQL/63681//url]

And then if you would like further assistance, post table definitions, sample data (non sensitive - but representative), and what you code you have attempted. In this manner I am sure you will receive, assistance which has been tested.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

rfranklin-741429 Old Hand Points: 348 More actions · Answer 2

Thanks for the link. I actually did read it earlier but will go thru it again. The version of SQL Server we are on does not support the PIVOT command so I wasn't able to try it.

I'll give it a shot again.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

From Jeff's article:

Last but not least, I currently only have SQL Server 2000 and 2005 installed. I indicate which rev each section of code will run on in parenthesis

Emphasis added by this poster

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

bitbucket-25253 (5/11/2010)
From Jeff's article:
Last but not least, I currently only have SQL Server 2000 and 2005 installed. I indicate which rev each section of code will run on in parenthesis
Emphasis added by this poster

You know what? I never noticed before but those particular notations in the code didn't show up for some reason. Looks like I need to do a little editing.

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

rfranklin-741429 (5/11/2010)
Thanks for the link. I actually did read it earlier but will go thru it again. The version of SQL Server we are on does not support the PIVOT command so I wasn't able to try it.
I'll give it a shot again.

If that's the case, don't use the PIVOT version... use the CROSS TAB version of the code which will work on many flavors of SQL, not just SQL Server.

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