查询SQL Server [英] query for SQL server

问题描述

大家好
我有如下的SQL表

Hi All
I have SQL table like below

Rack  Cell  Qty
A1     X     20
A2     Y     30
A3     Z     40
B1     X     59
B2     Z     60
C1     X     70

现在我的问题是如何显示如下表

Now my question is how I can display the table like below

   X  Y  Z
A1
A2
A3
B1
B2
C1

在原始表X，Y，Z中不止一次，但在新表中每次仅显示一次

感谢All

In original table X, Y, Z is more then one time but new table it shows only one time each

Thanks to All

推荐答案

尝试一下.它未经测试，因此如果有任何问题，请回复.

Try this. It is not tested so if any problem please reply.

select A.Rack,
 (select Sum(B.Qty) from tablename B  where B.Rack =A.Rack and B.Cell ='X') as 'X',
 (select Sum(B.Qty) from tablename B  where B.Rack =A.Rack and B.Cell ='Y') as 'Y',
 (select Sum(B.Qty) from tablename B  where B.Rack =A.Rack and B.Cell ='Z') as 'Z',
 Count(A.Rack)
from tablename A
group by A.Rack

您正在使用的SQL Server的版本.对于SQL 2005及更高版本，您想使用PIVOT.

有关更多详细信息，请参见:
http://www.mssqltips.com/sqlservertip/1019 /crosstab-queries-using-pivot-in-sql-server-2005/ [

Depends on the version of SQL Server you are using. For SQL 2005 and later you want to use PIVOT.

For more details see:
http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server-2005/[^]

This way you are not hardcoding the number of cells - if that grows the table of results automatically gets an extra column. Very similar to the CROSSTAB query in MS Access.

Alistair

select distinct A.Rack,
 (select B.Qty from temp B  where B.Rack =A.Rack and B.Cell like '%X%') as 'X',
 (select B.Qty from temp B  where B.Rack =A.Rack and B.Cell like '%Y%') as 'Y',
 (select B.Qty from temp B  where B.Rack =A.Rack and B.Cell like'%Z%') as 'Z'
from temp A

这篇关于查询SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！