Splitting a column into multiple sub-columns

Hello:

I am having trouble understanding the examples on how to split a column into multiple sub-columns

http://msdn.microsoft.com/en-us/library/ms157334(v=sql.100).aspx

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7d7b21d-4f9c-4235-b739-802ef1b241ca/splitting-a-column-in-tabular-report-into-multiple-subcolumns-in-ssrs-tabular-reports?forum=sqlreportingservices

Here is my SQL Statement below:

I want my end result to look something like this with all the months:
                Oct                      Nov                         Dec
            Y         N                 Y       N                   Y        N
John     5          3                3         2                  1         0
Joe       3          1                2        2                   0         4

                                   
SELECT StaffName, ExamWithin30Days,
SUM(OCTNO) AS OCTNO,
SUM(NOVNO) AS NOVNO,
SUM(DECNO) AS DECNO,
SUM(JANNO) AS JANNO,
SUM(FEBNO) AS FEBNO,
SUM(MARNO) AS MARNO,
SUM(APRNO) AS APRNO,
SUM(MAYNO) AS MAYNO,
SUM(JUNNO) AS JUNNO,
SUM(JULNO) AS JULNO,
SUM(AUGNO) AS AUGNO,
SUM(SEPNO) AS SEPNO,

SUM(OCTYES) AS OCTYES,
SUM(NOVYES) AS NOVYES,
SUM(DECYES) AS DECYES,
SUM(JANYES) AS JANYES,
SUM(FEBYES) AS FEBYES,
SUM(MARYES) AS MARYES,
SUM(APRYES) AS APRYES,
SUM(MAYYES) AS MAYYES,
SUM(JUNYES) AS JUNYES,
SUM(JULYES) AS JULYES,
SUM(AUGYES) AS AUGYES,
SUM(SEPYES) AS SEPYES

FROM
(
select StaffName, ExamWithin30Days
CASE WHEN M ='10' THEN SumYes ELSE 0 END AS OCTYES,
CASE WHEN M ='11' THEN SumYes ELSE 0 END AS NOVYES,
CASE WHEN M ='12' THEN SumYes ELSE 0 END AS DECYES,
CASE WHEN M ='1' THEN SumYes ELSE 0 END AS JANYES,
CASE WHEN M ='2' THEN SumYes ELSE 0 END AS FEBYES,
CASE WHEN M ='3' THEN SumYes ELSE 0 END AS MARYES, 
CASE WHEN M ='4' THEN SumYes ELSE 0 END AS APRTES,
CASE WHEN M ='5' THEN SumYes ELSE 0 END AS MAYYES,
CASE WHEN M ='6' THEN SumYes ELSE 0 END AS JUNYES,
CASE WHEN M ='7' THEN SumYes ELSE 0 END AS JULYES,
CASE WHEN M ='8' THEN SumYes ELSE 0 END AS AUGYES,
CASE WHEN M ='9' THEN SumYes ELSE 0 END AS SEPYES,

CASE WHEN M ='10' THEN SumNo ELSE 0 END AS OCTNO,
CASE WHEN M ='11' THEN SumNo ELSE 0 END AS NOVNO,
CASE WHEN M ='12' THEN SumNo ELSE 0 END AS DECNO,
CASE WHEN M ='1' THEN SumNo ELSE 0 END AS JANNO,
CASE WHEN M ='2' THEN SumNo ELSE 0 END AS FEBNO,
CASE WHEN M ='3' THEN SumNo ELSE 0 END AS MARNO, 
CASE WHEN M ='4' THEN SumNo ELSE 0 END AS APRNO,
CASE WHEN M ='5' THEN SumNo ELSE 0 END AS MAYNO,
CASE WHEN M ='6' THEN SumNo ELSE 0 END AS JUNNO,
CASE WHEN M ='7' THEN SumNo ELSE 0 END AS JULNO,
CASE WHEN M ='8' THEN SumNo ELSE 0 END AS AUGNO,
CASE WHEN M ='9' THEN SumNo ELSE 0 END AS SEPNO
FROM
(
SELECT StaffName, Y, M, SUM(case when ExamWithin30Days='Y' then 1 else 0 end) as SumYes, SUM(case when ExamWithin30Days='N' then 1 else 0 end) as SumN 
FROM Data
)
table1
)
table2

Open in new window

LVL 1
RecipeDanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming that you're doing a SUM and not a COUNT, something like this..
SELECT
  Name, 
  Sum(CASE WHEN month=10 AND some_column=1 THEN some_other_column ELSE 0 END) as oct_y, 
  Sum(CASE WHEN month=10 AND some_column=0 THEN some_other_column ELSE 0 END) as oct_n, 
  Sum(CASE WHEN month=11 AND some_column=1 THEN some_other_column ELSE 0 END) as nov_y, 
  Sum(CASE WHEN month=11 AND some_column=0 THEN some_other_column ELSE 0 END) as nov_n, 
  Sum(CASE WHEN month=12 AND some_column=1 THEN some_other_column ELSE 0 END) as dec_y, 
  Sum(CASE WHEN month=12 AND some_column=0 THEN some_other_column ELSE 0 END) as dec_n
FROM YourTable
GROUP BY name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
There is a syntax error on line 30 (missing coma)
The inner-most subquery is this:
        FROM (
                SELECT
                        StaffName
                      , Y
                      , M
                      , SUM(CASE
                                WHEN ExamWithin30Days = 'Y' THEN 1
                                ELSE 0
                                END) AS SumYes
                      , SUM(CASE
                                WHEN ExamWithin30Days = 'N' THEN 1
                                ELSE 0
                                END) AS SumN
                FROM Data
        )
        table1

Open in new window

but there is no group by, so using SUM(...) isn't necessary here.

These syntax issues lead me to believe you may have simplified your query to help us answer - but I would refer to see:
> the full query
> and some sample data

by the way is 'data' in  "FROM Data" really a table or is it a view or query result? e.g. is the column "M'' derived from a date field? (i.e. the month number derived from a date).

{+edit} and how do you ensure that data from previous years don't get included in the numbers (i.e. where do you filter by year? or isn't this relevant?)
0
ValentinoVBI ConsultantCommented:
I see you've been trying to use the Matrix, good, that would indeed make your query a lot easier.

So pass the raw data into your report, without any grouping/summing, and try this: your Row Group would be StaffName and your Column Groups are M and ExamWithin30Days (in that order).  You may also want to take Y into account (possibly concatenated with M in a calculated field on the dataset).

As Data you can specify something like =Count(1) which will give you the number of Ys and Ns within the M, as shown in your example.
0
RecipeDanAuthor Commented:
Thank you for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.