1

Good afternoon,

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

Rows to columns SQL Server query

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! : ).

Community
  • 1
  • 1
Eduardo
  • 19,928
  • 23
  • 65
  • 73
  • 1
    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 Aug 30 '12 at 13:05
  • 1
    Demonstration of the problem: http://www.sqlfiddle.com/#!3/0b577/2 – mellamokb Aug 30 '12 at 13:13

1 Answers1

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

mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • 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 http://stackoverflow.com/questions/7534403/rows-to-columns-sql-server-query – Eduardo Aug 30 '12 at 13:26
  • Hi @Eduardo: If you look at the [linked Demo](http://www.sqlfiddle.com/#!3/0b577/10), 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? – mellamokb Aug 30 '12 at 13:28
  • 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 Aug 30 '12 at 13:37
  • Í updated my question again, so noe the question is more clear sorry for the confusion. – Eduardo Aug 30 '12 at 13:45
  • @Eduardo: I see, yes it makes much more sense now :). I think `MahmoudGamal` had the correct answer, too bad he deleted it... http://www.sqlfiddle.com/#!3/41c79/3. **Edit**: Updated with an answer that should correct for your new explanation. – mellamokb Aug 30 '12 at 14:47
  • Superb mellamokb, I wish I can give you more reputation for all your help, thanks a lot!! my explanation wasn't clear at the beginning and you take the effort to rewrite the answer again, and reply also to my question about why it didn't work. – Eduardo Aug 30 '12 at 15:56