Query to fetch data from Table based on the Flag column [Resolved]

Posted by Somu475 under Sql Server on 4/1/2013 | Points: 10 | Views : 1353 | Status : [Member] | Replies : 5
Hi All,

I have a table named Conversion with 3 columns as YearMonth, ConvRate and ConvType.

Conversion:
YearMonth | ConvRate | ConvType
2012/01 | 1.75 | True
2012/01 | 1.73 | False
2012/02 | 1.80 | True
2012/02 | 1.82 | False
2012/03 | 1.85 | True
2012/03 | 1.88 | False

Where True=Actual and False=Estimated

Now I want to display the table as follows

Conversion:
YearMonth | Actual | Estimated
2012/01 | 1.75 | 1.73
2012/02 | 1.80 | 1.82
2012/03 | 1.85 | 1.88

How can I do that?
Please someone help me.

-Somu


Responses

Posted by: Somu475 on: 4/1/2013 [Member] Starter | Points: 25

Up
0
Down

Resolved
Hi,
I found a way to do that.

Query:
SELECT  YearMonth,

(SELECT ConvRate FROM Conversion AS T2
WHERE (ConvType = '0') AND (YearMonth = T1.YearMonth)) AS Actual,
(SELECT ConvRate FROM Conversion AS T3
WHERE (ConvType = '1') AND (YearMonth = T1.YearMonth)) AS Estimated
FROM Conversion AS T1
GROUP BY YearMonth


If anyone have different and easy way to do this, let me know.

-Somu

Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 4/3/2013 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Somu,

Try this using pivot

create table #convert(YearMonth varchar(10),ConvRate numeric(10,2),ConvType varchar(5))

insert into #convert values ('2012/01',1.75,'True'),
('2012/01' , 1.73 , 'False'),
('2012/02' , 1.80 , 'True'),
('2012/02' , 1.82 , 'False'),
('2012/03' , 1.85 , 'True'),
('2012/03' , 1.88 , 'False')

select YearMonth,True as Actual ,False as Estimated from
(select * from #convert)a pivot(sum(ConvRate) for ConvType in (True,False))b


Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/3/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

can you explain this pivot query?

Mark as Answer if its helpful to you

Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somu475 on: 4/3/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Kirthiga,

Your query is also working fine. But I don't want to use pivot.
Thanks for your reply.

-Somu

Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vedaraj on: 4/4/2013 [Member] Starter | Points: 25

Up
0
Down
Try this also

CREATE TABLE #Conversion (YearMonth VARCHAR(10),ConvRate NUMERIC(10,2),ConvType VARCHAR(5))


INSERT INTO #Conversion VALUES
('2012/01' ,1.75, 'True'),
('2012/01' , 1.73 , 'False'),
('2012/02' , 1.80 , 'True'),
('2012/02' , 1.82 , 'False'),
('2012/03' , 1.85 , 'True'),
('2012/03' , 1.88 , 'False')

SELECT a.YearMonth,Actual,Estimated FROM
(SELECT YearMonth,ConvRate AS Actual FROM #Conversion WHERE convtype='True') a
JOIN
(SELECT YearMonth,ConvRate AS Estimated FROM #Conversion WHERE convtype='False') b
ON a.YearMonth=b.YearMonth


vedaraj

Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response