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 : 2559 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	hi can you explain this pivot query? Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Somu475, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

0	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

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

Latest Posts