Adding a column in sql result set

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,
I have two table valued functions as T1 and T2
and executing this I get T1 data as

ParameterID	Parametername	ParameterDoc
1	French	French Data
2	German	German Data
3	Polish	Polish Data
4	English	English Data

and T2 as

CountryCode	Docxrelation
63	France
67	Germany
75	Poland
34	United States
65	India
66	China

and my expected result would be like

ParameterID	Parametername	ParameterDoc	CountryCode	Docx relation
1	French	French Data	63	France
2	German	German Data	67	Germany
3	Polish	Polish Data	75	Poland
4	English	English Data	34	United States
NULL	NULL	NULL	65	India
NULL	NULL	NULL	66	China

I tried to execute

Select * from dbo.T1, dbo.T2

but it does does gives me the expected result and gives more data than the expected result sets . there is no relation between their Id's/ countrycode that's why I'm getting issue to add column in the query result set.

Any suggestion will be highly appreciated.

Posted 7-Oct-15 23:31pm

Abhishek Pant

Add a Solution

Comments

Sreekanth Mothukuru 8-Oct-15 5:58am

You said "more data than the expected result set" is that because you are doing Select * instead of selective columns?

Abhishek Pant 8-Oct-15 6:38am

it becomes like data with cartesian product and I need all the columns as columns may vary in different conditions and I'm not known to column names as it is entered by a user from asp.net page

Sreekanth Mothukuru 8-Oct-15 8:03am

There is a concept called "Outer Apply" in Sql Server where you can join two tables horizontally. Try it in Google. It will fix your req.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ankur_Garg · Answer 1 · 2015-10-07T23:53:00

Solution 1

The above query would simply give a Cartesian product of the 2 tables. That means it would print 20 rows(4*5). To get the expected data, you should have a column matching in both the tables which isn't the case here. Try adding another table which contains the following information:

Table T3
Country Language
Germany German
France French
Poland Polish
United States English
India Hindi
China Mandarin

The new table would contain the country name and language columns. Now a mapping would be there to join the 3 tables and get the expected result.

Select T1.*,T2.* from T1
INNER JOIN T3 ON T1.Parametername= T3.Language
RIGHT OUTER JOIN T2 ON T2.Docxrelation = T3.Language

This would fetch the desired output.

Posted 7-Oct-15 23:53pm

Ankur_Garg

Comments

Abhishek Pant 8-Oct-15 6:06am

As per as my current condition the data is saved from a dynamic gridview have columns generated at runtime as according to parameterid for a table that I used in Tabular valued function. and as per as my requirement I have to show the expected result set in SSRS result set with the data I getting from that. So it would not be creating a relation if a user is entering rows and its columns dynamically from a gridview

[no name] 8-Oct-15 6:10am

Total no of Column and name will be same or it is different in different scenario?

Abhishek Pant 8-Oct-15 6:36am

yes it may vary in different scenario but at least one column will there as general prohibition.

Ankur_Garg 8-Oct-15 6:15am

There has to be a relation between both the tables to get the desired output. From where are the rows inserting into table T1 & T2?