以表格格式显示列 [英] show columns in a table format

问题描述

我在sql server 2005中有一个要求我有2个表格如下

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

请帮助我试过但无助请帮助。

谢谢

Srinivas

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

推荐答案

你不能用那些小的存储信息做到这一点 - 你需要一些共同的信息来关联两行价值的信息在一起。例如：

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:

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

可以工作 - 但它可能不会产生你真正想要的任何东西，因为SQL可以自由地按照它想要的任何顺序返回东西，所以它返回所有行的所有组合。因此，如果Table1有三行：

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

表2有四行：

And Table 2 has four rows:

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

然后上面的查询会给你12行：

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

你想要的是一些常见数据，因此Table1的第一行只与Table2的第d行相关联。

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

请参阅解决方案号1，了解为什么我没有准确地回答你想要的答案...

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

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...

请考虑解决方案1和2中给出的信息......

这可能会有所帮助....你必须根据你的要求检查你想要使用哪种类型的联接。 ..

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...

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:

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

这篇关于以表格格式显示列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！