displaying records horizontally

Question

displaying records horizontally

phamm

SSChasing Mays

Points: 629
More actions
November 12, 2007 at 10:38 pm

#180177

Please help.
I have a table with these columns:
a b c d
1 2 4 5
1 2 6 7
How would I display the columns so that it would be like this:
1 2 4 5 6 7
Greatly appreciate all the helps.

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

There must be a couple million posts on how to do something like this... and, most of them are a bad use of SQL 'cause this is a form of denormalization...

With that in mind, before I spend any time on this, you've gotta tell me what the real reason behind you wanting to do this is.

--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)
Intro to Tally Tables and Functions

phamm SSChasing Mays Points: 629 More actions · Answer 2

OK. So I have a fictitous view with these columns: buyer name, address, product number, product description (which is a join between purchaser and product table).

john doe, 123 usa, 1000, ski google

john doe, 123 usa, 2000, ski boot

Do I need to rewrite the view or come up with a new view to display:

john doe, 123 usa, 1000, ski google, 2000, ski boots, etc....

Thanks for your help in advance.

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

Oh, I understand what you want to do... but why do you want to do this to perfectly good data? Why do you need all of 1 person's info in a single comma delimited row?

--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)
Intro to Tally Tables and Functions

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

Seriously, Phamm...

I just wanna know before-hand for 2 reasons... will determine the method I use and I'm collecting "reasons" for this... just explain why you need to do this. Thanks.

--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)
Intro to Tally Tables and Functions

phamm SSChasing Mays Points: 629 More actions · Answer 5

Hi Jeff - I will be using this view to generate a report for accounting.

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

This is starting to be a little like pulling teeth, Phamm...

Why does the report need to be in this format? No fair saying the users want it that way... what the hell are they going to use it for? Are they going to feed some other piece of whacko code, third party skill management software, import to a spreadsheet??? WHAT???

Really would appreciate a real answer...

Anyway...

Let's say this small table represents the results of your view...

CREATE TABLE yourview

(

BuyerName VARCHAR(50),

BuyerAddress VARCHAR(50),

ProductNumber INT,

ProductDescription VARCHAR(50)

)

INSERT INTO yourview

(BuyerName, BuyerAddress,ProductNumber,ProductDescription)

SELECT 'john doe', '123 usa', 1000, 'ski google' UNION ALL

SELECT 'john doe', '123 usa', 2000, 'ski boot' UNION ALL

SELECT 'sally smith', '45 uk', 2000, 'ski boot' UNION ALL

SELECT 'sally smith', '45 uk', 2000, 'ski boot'

You need to create a function that looks like this...

CREATE FUNCTION dbo.fnConcatProduct

(@BuyerName VARCHAR(50),@BuyerAddress VARCHAR(50))

RETURNS VARCHAR(8000)

AS

BEGIN

--===== Declare local variables

DECLARE @Return VARCHAR(8000)

--===== Find and concatenate all the products for this buyer

SELECT @Return = ISNULL(@Return+',','')

+ CAST(ProductNumber AS VARCHAR(10))+','

+ ProductDescription

FROM yourview

WHERE BuyerName = @BuyerName

AND BuyerAddress = @BuyerAddress

ORDER BY ProductNumber

RETURN @Return

END

...and then use it like this...

SELECT DISTINCT

BuyerName, BuyerAddress, dbo.fnConcatProduct(BuyerName, BuyerAddress) AS yourstring

FROM yourview

Note the obvious limit of VARCHAR(8000)... if a Buyer has a million products, this isn't going to work... since this is in a 2k5 forum, you could prably get away with changing the @Return variable in the function to VARCHAR(MAX) to expand the capabilities.

I've done my part... like I said, please give me a real answer as to why some user wants data "reported" this way... what's it going to be used for????

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Since it's been a 3 ¹/₂ days since I asked, I don't feel bad about saying this, at all...

I knew it! What a jerk you are! Folks like me bust a hump for folks like you, but as soon as you get the code you want, you disappear instead of answering my very simple question? Shoot, you didn't even say "Thank you" 😛

Heh... but, the joke's on you Mr Stab-n-Grab... Since I was pretty sure you'd do this, I gave you the really slow version of the code just to see what happened when you got some working code... it has no real scalability and will absolutely die if the rowcounts go up... your DBA is gonna love you...

Good luck getting help from me in the future, checkvalve...

--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)
Intro to Tally Tables and Functions

phamm SSChasing Mays Points: 629 More actions · Answer 8

Mr Moden

Please read my before you starting to criticize me. Didn't I say "Greatly appreciate all the helps" in my first post????? Besides, I already very patiently telling you that all I know is that it is being used for an accounting report. We are all professional so PLEASE DON'T CALL PEOPLE NAME. It will only look bad on you.

Thank you sir.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

And, yet, my simple question goes unanswered. I just want to know why whoever you're writing this for thinks they need it in such a dumb format. Don't you have enough intellectual curiosity to ask them? 😉

--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)
Intro to Tally Tables and Functions

phamm SSChasing Mays Points: 629 More actions · Answer 10

curiosity is not the same as rude or unprofessional. In a real business world, you won't last for an hour with this kind of attitude. I'm really feel sorry for a sick person like you. Such a self-centered person who thinks he knows everything about sql server. What a clown.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Rude or unprofessional? Hell, you started it... first you wouldn't answer my simple question even though I gave you an answer that works... good forum etiquette dictates that you acknowlege the help after it is given and any other questions the respondent may have. To date, you've done neither.

And, I still don't have an answer to my simple question. 😉

--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)
Intro to Tally Tables and Functions

Robert O'Byrne SSCommitted Points: 1967 More actions · Answer 12

Okay, I'll try an answer. Hopefully this does not sound familiar.

Acct Guy: Hey DBA guy, I'm in accounting and I need a list of every purchases for last month by purchaser and item and include their address.

DBA Guy: OK, here ya go

john doe, 123 usa, 1000, ski google

john doe, 123 usa, 2000, ski boot

AcctGuy: No, no no. All wrong. My accounting report looks liks this;

john doe, 123 usa, 1000, ski google, 2000, ski boots, etc....

DBA Guy: I may be able to make something like this, but what do you want it for?

Acct Guy: And I also want to import it into excel and also my accounting package.

DBA Guy: You know that after I do this for you, you will start to use it and then find out working with it is difficult. No data structure or consistancy, you won't be able to sort or get counts from Excel. Excel will also fail to import it after 256 horizontal entries.

Acct Guy: Let me know when it's done.

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 13

I think most of the times developerrs have to produce a report out of bad database design. I think this is one of that situation.

In ideal world developer should not struggle with query at all. If the database is designed 100 percent perfect then all queries will be simple. More complex query indicates towards bad database design. I wish database designers were the same people writting SQLs against their design.:D

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Heh... well said Robert and ALI...

Yeup... that's kinda the way I figure it but we're only guessing...

The bad part here is a huge opportunity was missed to help the users that want the report. 😉

--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)
Intro to Tally Tables and Functions