show columns in a table format

Question

1.00/5 (2 votes)

See more:

SQL-Server-2005

i have a requirement in sql server 2005 i have 2 tables as follows

-------------------------------------
name                      JANclient
----------------------------------------
Srinivas                  ABC
suraj                     XYZ


------------------------------------
name                     FEBclient
--------------------------------------------
mahesh                   AAAA
prakash                  ZZZZZZZ

now i want the result table as shown below

-----------------------------------------------------------------------------------
name               JANclient              name                   FEBclient
-------------------------------------------------------------------------------------
Srinivas            ABC                   mahesh                  AAAA
suraj               XYZ                   prakash                 ZZZZZZZ

please help i had tried but helpless please helpme.
Thanks
Srinivas

Posted 27-Jun-13 6:00am

srinvas

Updated 27-Jun-13 11:22am

Maciej Los

v3

Add a Solution

Comments

[no name] 27-Jun-13 12:26pm

"i had tried but", you tried what exactly? What is the schema? What did you try to use to join the tables in your query?

CHill60 27-Jun-13 14:58pm

What is the problem if you know how to do it. Please post your code that can achieve this without a join across the tables as I'm intrigued
[Edit] In response to the 2nd comment - I doubt that it is possible

3 solutions

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)

OriginalGriff · Answer 1 · 2013-06-27T09:06:00

You can't do that with that little stored information - you need a piece of common information to relate the two rows worth of information together. For example:

SQL

SELECT a.*, b.* FROM Table1 a, Table2 b

Will work - sort of - but it probably won't produce anything you actually wanted because SQL is at liberty to return things in any order it wants, so it returns all combinations of all rows. So if Table1 has three rows:

1   AAA         2013-04-11 00:00:00.000
2   AAB         2013-03-01 00:00:00.000
3   AAC         2013-03-06 00:00:00.000

And Table 2 has four rows:

txt

10  522 2013-03-06 00:00:00.000
20  584 2013-03-06 00:00:00.000
30  584 2013-03-06 00:00:00.000
40  584 2013-03-06 00:00:00.000

Then the above query will give you 12 rows:

1   AAA         2013-04-11 00:00:00.000 10  522 2013-03-06 00:00:00.000
1   AAA         2013-04-11 00:00:00.000 20  584 2013-03-06 00:00:00.000
1   AAA         2013-04-11 00:00:00.000 30  584 2013-03-06 00:00:00.000
1   AAA         2013-04-11 00:00:00.000 40  584 2013-03-06 00:00:00.000
2   AAB         2013-03-01 00:00:00.000 10  522 2013-03-06 00:00:00.000
2   AAB         2013-03-01 00:00:00.000 20  584 2013-03-06 00:00:00.000
2   AAB         2013-03-01 00:00:00.000 30  584 2013-03-06 00:00:00.000
2   AAB         2013-03-01 00:00:00.000 40  584 2013-03-06 00:00:00.000
3   AAC         2013-03-06 00:00:00.000 10  522 2013-03-06 00:00:00.000
3   AAC         2013-03-06 00:00:00.000 20  584 2013-03-06 00:00:00.000
3   AAC         2013-03-06 00:00:00.000 30  584 2013-03-06 00:00:00.000
3   AAC         2013-03-06 00:00:00.000 40  584 2013-03-06 00:00:00.000

What you want is some common data, so row a of Table1 is associated only with row d of Table2.

Maciej Los · Answer 2 · 2013-06-27T11:36:00

Please, see the Solution no. 1 to understand the reason why i do not post answer exactly what you want...

SQL

SELECT [month], [name], client
FROM (
    SELECT 'Jan' AS [month], [name], JanClient AS client
    FROM Table1
    UNION ALL
    SELECT 'Feb' AS [month], [name], FebClient AS client 
    FROM Table2
    ) AS T
--ORDER BY [month], name

Result:

month    name        client
Jan      Srinivas    ABC
Jan      suraj       XYZ
Feb      mahesh      AAAA
Feb      prakash     ZZZZZZZ

Could it be?

I would suggest you to rethink the structure of database...

Raja Sekhar S · Answer 3 · 2013-06-27T19:55:00

Please Consider the info Given in Solution 1 and 2...
May be this can help.... U have to check which kind of join you want to use based on your Requirement...

SQL

Create Table #Table1 
	   (Name Nvarchar(50),
	    JANClient Nvarchar(50)
	   )

Create Table #Table2 
	   (Name Nvarchar(50),
	    FEBClient Nvarchar(50)
	   )

Insert into #Table1 (Name,JANClient)
Select 'Srinivas','ABC' Union
Select 'Suraj','XYZ'

Insert into #Table2 (Name,FEBClient)
Select 'Mahesh','AAAA' Union
Select 'Prakash','ZZZZZZZ'

Select a.Name,a.JANClient,b.Name,b.FEBClient From 
(Select Name,JANClient,ROW_NUMBER() Over(Order BY Name) as Nid From #Table1)a
Full Outer Join (Select Name,FEBClient,ROW_NUMBER() Over(Order BY Name) as Nid From #Table2)b 
on a.Nid=b.Nid 

Drop Table #Table1 
Drop Table #Table2

The Output:

SQL

Name	    JANClient     Name	   FEBClient
---------   ----------    --------  -----------
Srinivas	ABC	 Mahesh	     AAAA
Suraj	        XYZ	 Prakash     ZZZZZZZ

show columns in a table format

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0