Table design/select statements - rows to columns (T-SQL)?

Question

Table design/select statements - rows to columns (T-SQL)?

christian.rosberg

Newbie

Points: 3
More actions
January 16, 2010 at 1:03 am

#386118

Hi, I have a question regarding converting row values to columns and hope that someone could help me out. I have the following 3 tables (simplified):
Table Banks (Id, Name)
----------------------
1, Bank A
2, Bank B
Table Services (Id, Description)
-------------------------------
1, Service A
2, Service B
Table BankServices (Id, BankId, ServiceId, Cost)
-----------------------------------------------
1, 1, 1, 150
2, 1, 2, 225
3, 2, 1, 175
And I would like to get a resultset that looks similiar to this:
Services | Bank A | Bank B
-------------------------------
Service A | 150 | 175
-------------------------------
Service B | 225 | NULL
1. Is there an easy way to get the resultset I'd like, based on the table design I currently have?
2. Would you prefer an alternate table design?
Any help/comments much appreciated! 🙂
Cheers,
Christian

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

christian.rosberg (1/16/2010)
...
1. Is there an easy way to get the resultset I'd like, based on the table design I currently have?
2. Would you prefer an alternate table design?

Question 1:

Yes or no, depending on the definition of the term "easy" 😉

Based on your business case description you'll have a dynamic number of columns. Therefore, PIVOT won't help here. But, fortunately, this is a quite common issue, so there are solutions available. Please have a look at the DynamicCrossTab link I refer to in my signature.

Question 2:

Your table design is almost completely normalized.

To normalize it even further you'd have to split your table BankServices into two tables, one holding (Id, BankId, ServiceId) and the other one (BankServices ID, Cost).

But that wouldn't help nor harm the subject you asked for... I'd probably do it anyway....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Question 3:

Why do you want to denormalize the result this way? The answer could make a difference.

Also, what is the maximum number of banks you will handle in such a fashion.

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

christian.rosberg Newbie Points: 3 More actions · Answer 3

Thanks Lutz, will check out the DynamicCrossTab link! Might be a good idea no normalize the tables further, especially since I'll probably need some more data columns in the (old) BankServices table.

Question 3:
Why do you want to denormalize the result this way? The answer could make a difference.

Not really sure what you mean? I would like to be able to present the result as I have described, and I also want a table design that is normalized in a good way (I might want to use the different tables separately as well).

Also, what is the maximum number of banks you will handle in such a fashion.

The number of maximum banks may vary, but I think the max number initially will be around 15-20, but as I said that may change.

/Christian

GOC-481399 SSC Veteran Points: 249 More actions · Answer 4

"...I would like to be able to present the result..."

You might want to leave presentation for a presenation tool rather than doing it with TSQL. For example you could use Excel Pivot Tables or a Reporting Services Matrix - while keeping your underlying data normalised.

christian.rosberg Newbie Points: 3 More actions · Answer 5

You might want to leave presentation for a presenation tool rather than doing it with TSQL. For example you could use Excel Pivot Tables or a Reporting Services Matrix - while keeping your underlying data normalised.

Point taken. I will use ASP.NET for presenting my results thus I have the alternative to create some more presentation logic in this layer instead of using T-SQL.