Merging two rows

Hi All,
I have question about how to do select on below table.
This table has 3column,
Col1->Account Id[Values 1, 2, 3, 4]
Col2>Code [Values 1,2,3]
Col3>Amount[Values 100,200,300,400]
Each code maps to debit, credit, balance...
I want to do a select which can return me debit, credit and balance for
all the Account in the table.
Something like
select debit, credit, balance from Tab1.
But the diffculty is each debit, credit and balance are in a different
row. How can I merge many rows into a single select statement.
Please advise me how I can do this
Thank YouTry,
select
accountid,
sum(case when code = 1 then amount else 0 end) as debit,
sum(case when code = 2 then amount else 0 end) as credit,
sum(case when code = 3 then amount else 0 end) as balance
from
group by accountid
AMB
"dhani" wrote:

> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance for
> all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
>|||dhani wrote:
> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance
> for all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
Your requirements are not clear.
Please read and comply with www.aspfaq.com/5006
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The post by the user provides some information that should be able to help
him out.
Allthough I encourage the use of some 'standard' posting and
recommendations, just replying that the users requirements are not clear
and he should comply with etiquette that is not even requied by the
newsgroup doesn't make sense.
What the user wants is to pivot the table in this case.
Personally I would go for a different table design.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%234faQbWuFHA.3224@.TK2MSFTNGP10.phx.gbl...
> dhani wrote:
> Your requirements are not clear.
> Please read and comply with www.aspfaq.com/5006
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not clear
> and he should comply with etiquette that is not even requied by the
> newsgroup doesn't make sense.
Sure it does (and "make sense" is very subjective, isn't it? Just because
SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
all share that opinion). The requirements were not clear, so Bob asked for
further clarification, and showed the user a quite helpful link in providing
requirements in a generally accepted format here.
Nobody said it was required by the newsgroup (what is required by the
newsgroup anyway, I have no idea what you're talking about). But you'll see
this follow-up a lot, and I can only assume from you lack of visibility here
that it's just a fact of life in here that you haven't yet been exposed to
enough to understand.
21 letters. Wow.|||So you are judging my knowlegde?
I don't think we ever met and I would definetely not judge you, knowing that
you are a respected MVP.
Did you use a user defined function to count the letters in my
certification?
So why do you conclude I have a lack of visibility and what fact of life
have I not been exposed to?
Don't you think it would be better to just share knowledge in order to
provide solutions and results?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewD9UjXuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sure it does (and "make sense" is very subjective, isn't it? Just because
> SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
> all share that opinion). The requirements were not clear, so Bob asked
> for further clarification, and showed the user a quite helpful link in
> providing requirements in a generally accepted format here.
> Nobody said it was required by the newsgroup (what is required by the
> newsgroup anyway, I have no idea what you're talking about). But you'll
> see this follow-up a lot, and I can only assume from you lack of
> visibility here that it's just a fact of life in here that you haven't yet
> been exposed to enough to understand.
> 21 letters. Wow.
>|||> Don't you think it would be better to just share knowledge in order to
> provide solutions and results?
Yes, and part of that knowledge is learning how to post requirements that
work well, are easily re-create-able and test-able, and are requested.
You judged Bob's post and said it didn't make sense. I disagree. Period.|||Dandy Weyn [Dandyman] wrote:
> The post by the user provides some information that should be able to
> help him out.
I'm not trying to be combative or insulting, but, if the post contained
sufficient information, then why didn't you provide a solution?
Look, Alejandro made an attempt based on his guess as to what the user
needed. It may even be the correct solution. The question is: Who knows? I
could have made a similar attempt, only to have the OP come back and say:
"no that's not what I wanted at all", in which case both my time and the
OP's were wasted.

> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not
> clear and he should comply
Well, maybe "comply" was a poor choice of words: I meant "follow the
recommendations made" but I was a little strapped for time and was trying to
be concise.

> with etiquette that is not even requied by
I think if you use google, you will fiind that a large percentage of posts
to this group, as well as the comp.databases.ms-sqlserver group, receive a
similar initial request to provide DDL and sample data. This is usenet,
there is no way to "require" etiquette. That does not mean we should not
make an effort to help posters get help as efficiently as possible by
showing them the best way to provide the information needed to solve their
problems. Aaron's article does a good job of that. He put it in his
"ettiquette" section, but that does not mean this has anything to do with
"ettiquette".

> the newsgroup doesn't make sense.
Why not? What else should I do when the post contains insufficient
information to provide a solution? Waste my time and the OP's by making a
guess? Ignore the message (how does that help the OP)?
> What the user wants is to pivot the table in this case.
Maybe. In fact, I will even go so far as to say "probably". But, does he
need a dynamic pivot? Or does he have static values on which to base a
non-dynamic pivot?

> Personally I would go for a different table design.
Really? Based on what? You know very little about the OP's requirements ...
And why don't you think a vague statement about a "different table design"
is as bad as a request for clarification?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.