Adding Multiple Columns to existing Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding Multiple Columns to existing Table

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-31 : 10:28:48

Hello,

I developing one Report for that i need to combine 4, 5 queries data.for that i have one doubt.

My First Query Returning output as

Month Texas

Jan 100
Feb 110
Mar 120

My 2nd query returning

Atlanta
201
225
176

Want to combine two queries and my final output should be like this

Month Texas Atlanta

Jan 100 201
Feb 110 225
Mar 120 176

Can you please help me on this..

Archana

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 10:33:55

You should also get a month for the second dataset

select Mth = coalesce(t.month,a.month, texas = t.val, Atlanta = a.val
from
(select <texas query>) t
full outer join
(select <Atlanta query>) a
on a.month = t.month
order by Mth

Just put your queries into the above.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-31 : 11:08:51

Hi,

Thank you so much for youe reply.

i have written query as you mentioned

select Mth= coalesce(t.Month,a.Month,Texas= t.Val,Atlanta =a.Val)

but when i written like this getting error as

Incorrect syntax near '='

i tried like this also

select Mth= coalesce(t.Month,a.Month,t.Texas,a.Atlanta)

but getting error as

"Conversion failed when converting the nvarchar value 'Jan' to data type int"

Archana

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 11:17:10

sorry - I missed a bracket

select Mth = coalesce(t.month,a.month), texas = t.val, Atlanta = a.val
from
(select <texas query>) t
full outer join
(select <Atlanta query>) a
on a.month = t.month
order by Mth

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-31 : 11:24:48

Thank you so much..

Working now..

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:40:31

quote:
Originally posted by archana23

Thank you so much..

Working now..

Archana

another way


SELECT Month,SUM(val1) AS texas,SUM(val2) AS Atlanta
FROM
(
SELECT Month,val AS val1,CAST(0 AS decimal(10,2)) AS val2
FROM Query1
UNION ALL
SELECT Month,0,val
FROM Query2
)t
GROUP BY Month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 23:50:42

would u pls let me know about is the use of COALESCE clause ?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 23:53:12

quote:
Originally posted by mani_12345

would u pls let me know about is the use of COALESCE clause ?????

it accepts multiple arguments and returns first non null value among them

SELECT COALESCE(NULL,123.45,'test',NULL)

http://msdn.microsoft.com/en-us/library/ms190349.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 23:55:06

why u used coalesce as below query will also give u the same

select a.[month] ,a.atlanta ,b.texas from tab1_name a
full outer join
tab_name b
on a.[month] =b.[month]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 23:58:18

quote:
Originally posted by mani_12345

why u used coalesce as below query will also give u the same

select a.[month] ,a.atlanta ,b.texas from tab1_name a
full outer join
tab_name b
on a.[month] =b.[month]

because of full outer join. there can be chances were only one total is available for particular Month so in that case also for showing Month value you need to use coalesce and display it from both tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mani_12345
Starting Member

35 Posts

Posted - 2012-06-01 : 01:15:46

thnx for the response .. but i am not getting ur point in pratical ..will you please illustrate with an example :(

quote:
Originally posted by visakh16

quote:
Originally posted by mani_12345

why u used coalesce as below query will also give u the same

select a.[month] ,a.atlanta ,b.texas from tab1_name a
full outer join
tab_name b
on a.[month] =b.[month]

because of full outer join. there can be chances were only one total is available for particular Month so in that case also for showing Month value you need to use coalesce and display it from both tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:30:52

use this sample data and see


create table #table1
(
[Month] varchar(10),
Texas int
)
insert #table1
select 'Jan',100 union all
select 'Feb', 110 union all
select 'Mar', 120


create table #table2
(
[Month] varchar(10),
Atlanta int
)

insert #table2
select 'Jan',225 union all
select 'Mar', 230 union all
select 'Apr',110

now see the difference

SELECT t1.Month AS T1Month,
t2.Month AS T2Month,
COALESCE(t1.Month,t2.Month) AS MergedMonth,
Texas,
Atlanta
FROM #table1 t1
FULL JOIN #Table2 t2
on t2.[Month] = t1.[Month]

DROP TABLE #table1
DROP TABLE #table2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-03 : 04:44:29

One more way but not generalize:





CREATE TABLE #TAB1 ( [MONTH] VARCHAR(20),TEXAS INT )

INSERT INTO #TAB1 ([MONTH],TEXAS)
SELECT 'Jan',100
UNION ALL
SELECT 'Feb',110
UNION ALL
SELECT 'Mar',120



CREATE TABLE #TAB2 ( Atlanta INT )

insert into #TAB2 (Atlanta)
select 201
union all
select 225
union all
select 176

select * from #TAB1

select * from #TAB2

;with cte as
(
  select *,ROW_NUMBER()over (PARTITION by month order by (select null) ) rn from   
   ( select * from #TAB1 cross join #TAB2)t)
   
   select * from cte where ([Month] ='Jan'and rn=1) or ([Month] ='Feb'and rn=2)or
   ([Month] ='Mar'and rn=3)order by rn asc

Vijay is here to learn something from you guys.

mani_12345
Starting Member

35 Posts

Posted - 2012-06-03 : 23:38:29

oh i got your point ..
Thnx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources