October 16, 2013 at 7:49 pm
I am creating a Report that uses SQL Statement to select Rows and I need to add alphabets like A-->Row one, B-->Row two etc. Below is my query
SELECT table.text1, table2.text2
FROM table1 RIGHT OUTER JOIN
table2 ON table1.tableID = table2.tableID LEFT O
WHERE (table1.mycolumn = 0) AND (table2.mycolumn2 = 0)
October 16, 2013 at 8:34 pm
We don't have your tables and data in our database so It is better for us if you have codes to generate those NEXT TIME when asking for help. That way we can spend our time helping you with your problem instead of trying to figure out your underlying tables and data.
Anyway, here is my take on your request.
--PREPARING YOUR UNDERLYING TABLES AND VALUES
declare @table1 table(
tableID int,
text1 varchar(10),
mycolumn int
)
declare @table2 table(
tableID int,
text2 varchar(10),
mycolumn2 int)
insert into @table1 values (1,'text1_0', 0)
insert into @table1 values (2,'text2_0', 0)
insert into @table1 values (3,'text3_0', 0)
insert into @table1 values (3,'text4_1', 0)
insert into @table2 values (1,'text1_0',0)
insert into @table2 values (2,'text2_0',0)
insert into @table2 values (3,'text3_0',0)
insert into @table2 values (4,'text4_0',0)
insert into @table2 values (4,'text5_1',1)
-- QUERY TO DISPLAY RESULT YOU ARE LOOKING FOR
select text1,
text2,
YourrequestedColumn =
case RowNumber
when 1 then 'A'
when 2 then 'B'
when 3 then 'C'
when 4 then 'D'
when 5 then 'D'
when 6 then 'F'
ELSE
'GOOD LUCK'
END
FROM (
SELECT row_number() OVER(ORDER BY table1.text1) RowNumber, table1.text1, table2.text2
FROM @table1 table1 RIGHT OUTER JOIN
@table2 table2 ON table1.tableID = table2.tableID
WHERE (table1.mycolumn = 0) AND (table2.mycolumn2 = 0))
T1
October 16, 2013 at 9:08 pm
Thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply