Rows to columns in SQL being one of the columns a count

Question

Good afternoon,

I am trying to use CASE like in this question to transpose rows to columns:

However in my case I don't have a column with information it's a count of data.

I obtain the data with this query:

select MA, SN, count(*) 
from Original
where MA = 'AB'
group by MA

Result of the query:

MA  SN      COUNT
AB  TEXTA   6
AB  TEXTB   5
AB  TEXTC   3
AB  TEXTD   4

table Original

MA  SN      
AB  TEXTA   
AB  TEXTA   
AB  TEXTA   
AB  TEXTA   
AB  TEXTA   
AB  TEXTA   
.
.
.
AB  TEXTD   
AB  TEXTD

table Result:

MA      TEXTA   TEXTB   TEXTC   TEXTD
AB      6       5       3       4

And this is my current query:

select  MA,
        count(*) as 'COUNT2',
    MAX(CASE WHEN SN = 'TEXTA' THEN COUNT2 end) as TEXTA1,
    MAX(CASE WHEN SN = 'TEXTB' THEN COUNT2 end) as TEXTB1,
    MAX(CASE WHEN SN = 'TEXTC' THEN COUNT2 end) as TEXTC1,
    MAX(CASE WHEN SN = 'TEXTD' THEN COUNT2 end) as TEXTD1,

from Original
where MA= 'AB'
group by MA

WHat am I doing horribly wrong in the query?. And can someone please explain why in the question I posted it works but not with the count?. Thank you! : ).

It's microsoft SQL, I don't see the typo in my query I am writing as COUNT2 and then in the case COUNT2. If you are referring to the table Original, that's obtained with another query and I can write any name I want. Thank you. — Eduardo
– Eduardo, Commented Aug 30, 2012 at 13:05

mellamokb · Accepted Answer · 2012-08-30 14:49:37Z

4

Edit: You can't use COUNT2 in the way you intend because later columns in the select list cannot refer to aliases of previous columns. So COUNT2 has no meaning anywhere else in the query, unless you convert that part into a subquery (or cte) and then refer to it.

So, with your updated schema, it makes sense that you will need a subquery to do the counts. Essentially drop your first query as a subquery to the second query (in replace of Original), and then add a few missing group by columns, and then it works:

select  MA,
    MAX(CASE WHEN SN = 'TEXTA' THEN COUNT2 end) as TEXTA1,
    MAX(CASE WHEN SN = 'TEXTB' THEN COUNT2 end) as TEXTB1,
    MAX(CASE WHEN SN = 'TEXTC' THEN COUNT2 end) as TEXTC1,
    MAX(CASE WHEN SN = 'TEXTD' THEN COUNT2 end) as TEXTD1
from (
    select MA, SN, count(*) as COUNT2
    from Original
    where MA = 'AB'
    group by MA, SN
) Original
where MA= 'AB'
group by MA

http://www.sqlfiddle.com/#!3/41c79/7

edited Aug 30, 2012 at 14:49

answered Aug 30, 2012 at 13:17

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Eduardo Over a year ago

Hi thanks for the answer, as mentioned in the question "I don't have a column with information it's a count of data". I wrote a sample query to obtain the data. This is the difference between my question and this one stackoverflow.com/questions/7534403/…

mellamokb Over a year ago

Hi @Eduardo: If you look at the linked Demo, you'll see that it transforms your table Original to the expected output you have in Results. Can you explain if this is not the desired result?

Eduardo Over a year ago

Hi I checked the query and you are writing manually the values 6, 5, 4 and 3, that in my case can change becuase they are the result of a count operation. The table Original should be like this CREATE TABLE Original ('AB', 'TEXTA'), ('AB', 'TEXTA'), ('AB', 'TEXTA'), ('AB', 'TEXTA'), ('AB', 'TEXTA'), ('AB', 'TEXTA'), ('AB', 'TEXTB'), ('AB', 'TEXTB'), ('AB', 'TEXTB'), ('AB', 'TEXTB'), ('AB', 'TEXTB'), ('AB', 'TEXTC'), ('AB', 'TEXTC'), ('AB', 'TEXTC'), ('AB', 'TEXTD'); ('AB', 'TEXTD'); ('AB', 'TEXTD'); ('AB', 'TEXTD');

Eduardo Over a year ago

Í updated my question again, so noe the question is more clear sorry for the confusion.

mellamokb Over a year ago

@Eduardo: I see, yes it makes much more sense now :). I think MahmoudGamal had the correct answer, too bad he deleted it... sqlfiddle.com/#!3/41c79/3. Edit: Updated with an answer that should correct for your new explanation.

|

Collectives™ on Stack Overflow

Rows to columns in SQL being one of the columns a count

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related