March 4, 2013 at 12:37 am
below is the code
DROP TABLE Class,Amount
create table Class
(
code int,
CName varchar(10)
)
create table Amount
(
code int,
Currency char(3),
Amount float
)
insert into Class
select 1 , 'ASD' UNION
select 2 , 'SSS' UNION
select 3 , 'BBB' UNION
select 4 , 'EEE' UNION
select 5 , 'MMM'
insert into Amount
select 3, 'CAD',230.00 UNION
select 5, 'CAD',440.00 UNION
select 1, 'CAD',666.00 UNION
select 5, 'USD',40.00 UNION
select 2, 'USD',66.00
select * from Class
select * from Amount
Desired output
Code Currency Amount
ASDCAD666
SSSCADNA
BBBCAD230
EEECADNA
MMMCAD440
ASDUSDNA
SSSUSD66
BBBUSDNA
EEEUSDNA
MMMUSD40
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2013 at 1:00 am
Easy peasy:
if exists(select 1 from sys.tables where object_id = object_id('dbo.Class'))
DROP TABLE dbo.Class;
if exists(select 1 from sys.tables where object_id = object_id('dbo.Amount'))
DROP TABLE dbo.Amount;
create table dbo.Class
(
code int,
CName varchar(10)
);
create table dbo.Amount
(
code int,
Currency char(3),
Amount float
);
insert into dbo.Class
select 1 , 'ASD' UNION
select 2 , 'SSS' UNION
select 3 , 'BBB' UNION
select 4 , 'EEE' UNION
select 5 , 'MMM';
insert into dbo.Amount
select 3, 'CAD',230.00 UNION
select 5, 'CAD',440.00 UNION
select 1, 'CAD',666.00 UNION
select 5, 'USD',40.00 UNION
select 2, 'USD',66.00;
with Currency as (
select distinct
Currency
from
dbo.Amount
), ClassCurrency as (
select
cl.code,
cl.CName,
cur.Currency
from
dbo.Class cl
cross join Currency cur
) --select * from ClassCurrency
select
cc.CName,
cc.Currency,
coalesce(cast(am.Amount as varchar), 'NA') as Amount
from
ClassCurrency cc
left outer join dbo.Amount am
on (cc.code = am.code and cc.Currency = am.Currency)
go
if exists(select 1 from sys.tables where object_id = object_id('dbo.Class'))
DROP TABLE dbo.Class;
if exists(select 1 from sys.tables where object_id = object_id('dbo.Amount'))
DROP TABLE dbo.Amount;
go
March 4, 2013 at 1:15 am
Lynn beat me to it by a minute or two, but here's another way:
create table #Class
(code int,
CName varchar(10))
create table #Amount
(code int,
Currency char(3),
Amount float)
insert into #Class
select 1 , 'ASD' UNION ALL select 2 , 'SSS' UNION ALL
select 3 , 'BBB' UNION ALL select 4 , 'EEE' UNION ALL
select 5 , 'MMM'
insert into #Amount
select 3, 'CAD',230.00 UNION ALL select 5, 'CAD',440.00 UNION ALL select 1, 'CAD',666.00 UNION
select 5, 'USD',40.00 UNION ALL select 2, 'USD',66.00
select a.CName, b.Currency
,CASE WHEN Amount IS NULL THEN 'NA' ElSE CAST(Amount AS VARCHAR(20)) END
from #Class a
CROSS JOIN (SELECT DISTINCT Currency FROM #Amount) b
LEFT JOIN #Amount c ON a.code = c.code AND b.Currency = c.Currency
DROP TABLE #Class,#Amount
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2013 at 1:30 am
Thanks Dwain and lynn 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2013 at 1:50 am
SELECT cls.Cname as Code,Amnt.Currency,ISNULL(Amnt.Amount,'NA') Amount
FROM CLASS cls FULL JOIN Amount Amnt ON cls.code=Amnt.Code
ORDER BY Amnt.Currency
Try the above one...
March 4, 2013 at 6:56 am
Pulivarthi Sasidhar (3/4/2013)
SELECT cls.Cname as Code,Amnt.Currency,ISNULL(Amnt.Amount,'NA') AmountFROM CLASS cls FULL JOIN Amount Amnt ON cls.code=Amnt.Code
ORDER BY Amnt.Currency
Try the above one...
First, your code as posted fails with this error:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to float.
Second, when corrected the following is the result set returned:
CodeCurrencyAmount
EEENULLNA
MMMCAD440
ASDCAD666
BBBCAD230
SSSUSD66
MMMUSD40
Not what was requested.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply