Matrix Help with Columns

sql server 2005

Hi all,

I have a finacial report that I need to show all periods 1-12 (columns) regardless if there is data or not. what i am getting is

account--1--5--6

a#1235--#-" "-#

a#2346-" "-#--#

what i want is

account--1--2--3--4--5--6--7--8--9--10--11--12

even if the accounts 1235 and 2346 only have data for a couple of periods i want all periods to show on the report.

is this possible? can someone help me please, tell me what to do or point me to an article?

Thanks in advanced,

Kerrie

Kerrie,

Is there a reason you're not using a table instead? With a table, you specify exactly what columns you want to show, and the number of rows is variable based on the number of accounts that are returned in your dataset.

-Jessica

|||

You could use a common table expression and an outer join to make sure that there are always periods 1-12

The query would look like this:

with period_cte (period) as
(select 1 as period
union
select 2 as period
union
select 3 as period
union
select 4 as period
union
select 5 as period
union
select 6 as period
union
select 7 as period
union
select 8 as period
union
select 9 as period
union
select 10 as period
union
select 11 as period
union
select 12 as period)

select p.period, <other fields> .... from period_cte p left outer join SomeTable t on p.period = t.period