how to put merge column name of three table in other table : The Official Microsoft ASP.NET Forums

Reply

how to put merge column name of three table in other table

Apr 19, 2012 01:45 PM|LINK

Hi,

I have three tables which are FirstForm, SecondForm and ThirdForm.

FirstForm

FormName FormOrganisation FormDesignation FormOfficeTel FormMobile

abc xyz efg 121212 121212

SecondForm

SecondFormName SecondFormOrganisation SecondFormDesignation SecondFormOfficeTel SecondFormMobile

bbb ccc eee 111111 121111

ThirdForm

ThirdFormName ThirdFormOrganisation ThirdFormDesignation ThirdFormOfficeTel ThirdFormMobile

ttt aaa nnn 333333 222222

I merge in one column for there tables by using sql which is look like

Name Organisation Designation OfficeTel Mobile

abc xyz efg 121212 121212

bbb ccc eee 111111 121111

ttt aaa nnn 333333 222222

But I would like to put these merge column name into other table's column name

I means after i merge three table by sql how shall i put that already merge column name

in the other table including the datafield.

eg. there is table "FourthForm"

under this table

PAName PADesigantion PAOrganisation PAEmail

uu qq www w@g.com

But I would like to be like this

Name Organisation Designation OfficeTel Mobile PAName PADesignation PAOrganisation PAEmail

However, Name,Organisation,Designation,OfficeTel and Mobile are not come from

table they are come from merge three tables.

Please, can anyone advice me or give me anyidea how to get like this. Thanks in advance.

Reply

Re: how to put merge column name of three table in other table

Apr 19, 2012 02:15 PM|LINK

Hi,

Try:

with CTE_Union as
(
select
    FormName as Name,
    FormOrganisation as Organization,
    FormDesignation as Designation,
    FormOfficeTel as OfficeTel,
    FormMobile as Mobile
from FirstForm

union all

select
    SecondFormName,
    SecondFormOrganisation,
    SecondFormDesignation,
    SecondFormOfficeTel,
    SecondFormMobile
from SecondForm

union all

select
    ThirdFormName,
    ThirdFormOrganisation,
    ThirdFormDesignation,
    ThirdFormOfficeTel,
    ThirdFormMobile
from ThirdForm
)

select
    u.Name,
    u.Organization,
    u.Designation,
    u.OfficeTel,
    u.Mobile,
    f.PAName,
    f.PADesignation,
    f.PAOrganisation,
    f.PAEmail
from CTE_Union as u
join FourthForm as f on f.PAName = u.Name

I hope this helps.

http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos

Reply

Re: how to put merge column name of three table in other table

Apr 19, 2012 02:46 PM|LINK

Hi,

Sorry, I post it wrongly. Must be this one that I would like to know.

I have three tables which are FirstForm, SecondForm and ThirdForm.

FirstForm

FormName FormOrganisation FormDesignation FormOfficeTel FormMobile

abc xyz efg 121212 121212

SecondForm

SecondFormName SecondFormOrganisation SecondFormDesignation SecondFormOfficeTel SecondFormMobile

bbb ccc eee 111111 121111

ThirdForm

ThirdFormName ThirdFormOrganisation ThirdFormDesignation ThirdFormOfficeTel ThirdFormMobile

ttt aaa nnn 333333 222222

I merge in one column for there tables by using sql which is look like

Name Organisation Designation OfficeTel Mobile

abc xyz efg 121212 121212

bbb ccc eee 111111 121111

ttt aaa nnn 333333 222222

But I would like to put these merge column name into other table's column name

I means after i merge three table by sql how shall i put that already merge column name

in the other table including the datafield.

eg. there is table "FourthForm"

under this table

PAName PADesigantion PAOrganisation PAOfficeTel PAMobile

uu qq www 11111 22222

But I would like to be like this

______________________________________________________________________________________

| PAName | PADesignation PAOrganisation PAOfficeTel PAMobile |

______________________________________________________________________________________

| uu | qq | www | 11111 | 22222 |

________________________________________________________________________________________

I would like to put the merge column name in the value of the column of the another table.

Please, could anyone advice me or any idea how to sort like this. Thanks in advance.

Reply

Re: how to put merge column name of three table in other table

Apr 19, 2012 03:09 PM|LINK

Hi,

I do not know if I understand correctly but try this:

with CTE_Union as
(
    select
        FormName as Name,
        FormOrganisation as Organization,
        FormDesignation as Designation,
        FormOfficeTel as OfficeTel,
        FormMobile as Mobile
    from FirstForm

    union all

    select
        SecondFormName,
        SecondFormOrganisation,
        SecondFormDesignation,
        SecondFormOfficeTel,
        SecondFormMobile
    from SecondForm

    union all

    select
        ThirdFormName,
        ThirdFormOrganisation,
        ThirdFormDesignation,
        ThirdFormOfficeTel,
        ThirdFormMobile
    from ThirdForm
)

select
    PAName,
    PADesignation,
    PAOrganisation,
    PAOfficeTel,
    PAMobile
from FourthForm

union all

select
    Name,
    Designation,
    Organization,
    OfficeTel,
    Mobile
from CTE_Union

I hope this helps.

http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos

Reply

Re: how to put merge column name of three table in other table

Apr 19, 2012 03:26 PM|LINK

Hi,

Not really like this that I want. I means

I have three tables which are FirstForm, SecondForm and ThirdForm.

FirstForm

FormName FormOrganisation FormDesignation FormOfficeTel FormMobile

abc xyz efg 121212 121212

SecondForm

SecondFormName SecondFormOrganisation SecondFormDesignation SecondFormOfficeTel SecondFormMobile

bbb ccc eee 111111 121111

ThirdForm

ThirdFormName ThirdFormOrganisation ThirdFormDesignation ThirdFormOfficeTel ThirdFormMobile

ttt aaa nnn 333333 222222

I merge in one column for there tables by using sql which is look like

MergeName MergeOrg MergeDesg MergeOffTel MergeMobile

abc xyz efg 121212 121212

bbb ccc eee 111111 121111

ttt aaa nnn 333333 222222

But I would like to put these merge column name into other table's column name

I means after i merge three table by sql how shall i put that already merge column name

I want mearge column name as a record or datavalues of another table column name.

eg. FourthTable

TableFieldName TableFieldOrganisaiton TableFieldDesignation TableFieldOfficeTel TableFieldMobile --------- Column Name

OneName OneOrganisation OneDesignation OneOfficeTel OneMobile --------- DataFields of Col

twoName twoOrg twoDesg twoOffTel twoMobile --------- DataFields of Col

MergeName MergeOrg MergeDesg MergeOffTel MergeMobile --------- DataFields of Col

I would like to put merge column name as the datafields under another table's Column name.

Please, kindly advice me or give me anyidea. Thanks in advance.

Reply

Re: how to put merge column name of three table in other table

Apr 19, 2012 04:07 PM|LINK

Sounds like you want to concatonate fields.

Reply

Qin Dian Tan...

All-Star

113532 Points

12480 Posts

Microsoft

Re: how to put merge column name of three table in other table

Apr 23, 2012 08:23 AM|LINK

Hi,

Do you want to add the column names from one table (merged one) into another table (the fouth one) as the data, not columnname? If so, I think you have to write this in code (I am not sure how to do it in sql). In code you can return data from merged table into a DataTable, then you can get its column names. Then add these data into the new table with the data from the fouth one.

Thanks,

Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contactmsdnmg@microsoft.com.
Microsoft One Code Framework

Reply

Junior1012

Member

76 Points

178 Posts

Re: how to put merge column name of three table in other table

Apr 24, 2012 05:57 AM|LINK

Hi,

Thanks for your idea guys, I sort this out like below myself

ALTER PROC [dbo].[Event_Report]

AS
BEGIN
select * from
(select p.NominatorOrganisation as Event_Att_Name from POAForm p
union
select i.Name from InKindSponsorshipForm i
union
select n.BeneficiarySponsorName from NominationDetailsForm n)as A
CROSS JOIN
(select p.NominatorDesignation as Event_Att_Designation from POAForm p
union
select i.Designation from InKindSponsorshipForm i
union
select n.Designation from NominationDetailsForm n)AS B
CROSS JOIN
(select p.ContactNoOne as Event_Att_OfficeTel from POAForm p
union
select i.ContactNoOne from InKindSponsorshipForm i
union
select n.ContactNoOne from NominationDetailsForm n)AS C
CROSS JOIN
(select p.ContactNoTwo as Event_Att_Mobile from POAForm p
union
select i.ContactNoTwo from InKindSponsorshipForm i
union
select n.ContactNoTwo from NominationDetailsForm n)AS D
CROSS JOIN
(select p.NominatorEmail as Event_Att_Email from POAForm p
union
select i.Email from InKindSponsorshipForm i
union
select n.Email from NominationDetailsForm n)AS E
CROSS JOIN

(select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization,
tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join
NominationDetailsForm n on p.POAFormID = n.POAFormID
where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
and tp.DeleteStatus='N') AS F
END

And put the colulm name in my DB tabe which means I merge table in sql and give column name

and put straight away in the Report table.

Marked as answer by Junior1012 on Apr 24, 2012 05:57 AM

how to put merge column name of three table in other table RSS

7 replies

Junior1012

how to put merge column name of three table in other table

gimimex

Re: how to put merge column name of three table in other table

Junior1012

Re: how to put merge column name of three table in other table

gimimex

Re: how to put merge column name of three table in other table

Junior1012

Re: how to put merge column name of three table in other table

Dan Bracuk

Re: how to put merge column name of three table in other table

Qin Dian Tan...

Re: how to put merge column name of three table in other table

Junior1012

Re: how to put merge column name of three table in other table

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft