colAName  |  colBValue
--------     ---------
A           1
C           2
F           3
H           4

A      1
B      1
C      2
D      2
E      2
F      3
G      3

quote:

---

Originally posted by afrika

Use the order clause in your query and order columen colAName asc

---

quote:

---

Originally posted by pravin14u

quote:

---

Originally posted by afrika

Use the order clause in your query and order columen colAName asc

---

The Requirement is that, even if you dont have any alphabets, those should be dissplayed with the value of last existing alphabet

if Cols have

A-1
C-2

B should also be displayed in the result with A's Value(i,e)

A-1
B-1
C-2
etc.,

Prakash.P
The secret to creativity is knowing how to hide your sources!

---

declare @t table (colAName char(1), colBValue int)
insert @t
          select 'A', 1
union all select 'C', 2
union all select 'F', 3
union all select 'H', 4

declare @letters table (x char(1))
insert @letters 
select distinct char(number + 64) from master.dbo.spt_values where number between 1 and 26

select x, max(colBValue) as y from @letters inner join @t on x >= colAName
where x < (select max(colAName) from @t)
group by x order by x

--Preparation (you may not need some of this if you have it already)
DECLARE @Months TABLE (MonthNumber int, MonthName varchar(10))

INSERT INTO @Months (MonthNumber)
          SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12

update @Months set MonthName = datename(month, dateadd(month, MonthNumber-1, 0))

declare @t table (MonthName varchar(10), colBValue int)
insert @t
          select 'January', 1
union all select 'March', 2
union all select 'June', 3
union all select 'August', 4

--Query (the bit you need)
select a.MonthNumber, a.MonthName, max(b.colBValue) as colBValue
from @Months a inner join @t b on a.MonthNumber >= month(cast('1 ' + b.MonthName + ' 1900' as datetime))
group by a.MonthNumber, a.MonthName order by a.MonthNumber

/* results
MonthNumber MonthName  colBValue
----------- ---------- -----------
1           January    1
2           February   1
3           March      2
4           April      2
5           May        2
6           June       3
7           July       3
8           August     4
9           September  4
10          October    4
11          November   4
12          December   4
*/