SQL語句問題-行列調換 ( 积分: 100 )

如何使一個表/查詢結果集的行和列完全調換過來的語句？
如
1000 1.1 1.2
2000 2.1 2.2
3000 3.1 3.2
調換成
1000 2000 3000
1.1 2.1 3.1
1.2 2.2 3.2
表的記錄條數是隨機的

如何使一個表/查詢結果集的行和列完全調換過來的語句？
如
1000 1.1 1.2
2000 2.1 2.2
3000 3.1 3.2
調換成
1000 2000 3000
1.1 2.1 3.1
1.2 2.2 3.2
表的記錄條數是隨機的

关注这个问题呀。帮你顶一下。

你这是个交叉表的问题，但交叉表也是有条件的，你应该把问题说清楚一些，包括列名和交叉条件。

看我的動態實現交叉表，不需要知道列名
http://www.cnblogs.com/bonny.wong/archive/2005/01/23/96124.html

非原創，只是拿出來，供大家參考，希望對大家有用：)
有时候需要旋转结果以便在水平方向显示列，而在垂直方向显示行。这就是所谓的创建 PivotTable®、创建交叉数据报表或旋转数据。
假定有一个表 Pivot，其中每季度占一行。对 Pivot 的 SELECT 操作在垂直方向上列出这些季度：
Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
生成报表的表必须是这样的，其中每年占一行，每个季度的数值显示在一个单独的列中，如：
Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4

下面的语句用于创建 Pivot 表并在其中填入第一个表中的数据：
USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
下面是用于创建旋转结果的 SELECT 语句：
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
该 SELECT 语句还处理其中每个季度占多行的表。GROUP BY 语句将 Pivot 中一年的所有行合并成一行输出。当执行分组操作时，SUM 聚合中的 CASE 函数的应用方式是这样的：将每季度的 Amount 值添加到结果集的适当列中，在其它季度的结果集列中添加 0。
如果该 SELECT 语句的结果用作电子表格的输入，那么电子表格将很容易计算每年的合计。当从应用程序使用 SELECT 时，可能更易于增强 SELECT 语句来计算每年的合计。例如：
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
带有 CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 都计算与本例显示相同的信息种类，但格式稍有不同。

另一种法子
标题 在Delphi中自己建立交叉表 szlifei（原作）

关键字 Delphi 交叉表



经常在CSDN上查阅名位大侠的文章，得益不少，近期因做一个项目，需要用到交叉表，报表上倒是有，但客户要求在Grid上能操作，没有办法，只好自己写了一段代码用于普通查询到交叉表的实现，不敢独享，故上传，望能抛砖引玉，请名位大侠不吝指教。


function CreateTmptab(const AFieldDefs:TFieldDefs):TDataSet;
var
TempTable:TatClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefs<>nil then
begin
try
TempTable:=TatClientDataSet.Create(Application);
TempTable.FieldDefs.Assign(AFieldDefs);
TempTable.CreateDataSet;
Result:=(TempTable as TDataSet);
Except
if TempTable<>nil then
TempTable.Free;
Result:=nil;
raise;
end
end;
end;
{
SouDataset源数据集
ColField交叉表动态列字段
RowField交叉表行字段
DataField数据字段
}
function GenCrossTable(SouDataset:tdataset;ColField,RowField,DataField:string):tdataset;
var
Vdataset:tdataset;
tmpdataset:tatclientdataset;
DataSource:tdatasource;
tmpstrs:tstrings;
rowval,colval,dataval:string;
i,j:integer;
datatype:TFieldType;
DataSize:integer;
begin
result:=nil;
if (ColField='') or(RowField='')or(DataField='') then
showmessage('All Field not be NULL!')
else
begin
if (ColField=RowField)
or(ColField=DataField)
or(RowField=DataField) then
showmessage('All Field not be Equ!')
else
if (self.SouDataSet.FieldByName(ColField).DataType=ftString)
or (self.SouDataSet.FieldByName(ColField).DataType<>ftWideString)
or (self.SouDataSet.FieldByName(ColField).DataType<>ftFixedChar)
or (self.SouDataSet.FieldByName(ColField).DataType<>ftMemo)
or (self.SouDataSet.FieldByName(ColField).DataType<>ftFmtMemo) then
begin
try
tmpstrs:=tstringlist.Create;
Vdataset:=SouDataSet;
Vdataset.First;
for i:=0 to Vdataset.RecordCount-1 do
begin
if (varisnull(SouDataSet.FieldValues[colfield])=false) and (SouDataSet.FieldValues[colfield]<>'') then
if tmpstrs.IndexOf(SouDataSet.FieldValues[colfield])=-1 then
begin
tmpstrs.Add(SouDataSet.FieldValues[colfield]);
end;
Vdataset.Next;
end;
//生成动态列标题
tmpdataset:=TClientDataSet.Create(Self);
tmpdataset.FieldDefs.Add(rowfield,ftstring,50,False);
for i:=0 to tmpstrs.Count-1 do
begin
with tmpdataset.FieldDefs do
begin
Add(tmpstrs.Strings,ftInteger,0,False);
end;
end;
tmpdataset.FieldDefs.Add('Sum',ftInteger,0,False);
DataSource:=tdatasource.Create(self);
DataSource.DataSet:=tmpdataset;
with DataSource do
begin
dataset:=Createtmptab(tmpdataset.FieldDefs);
dataset.Open;
end;
//建立临时表
Vdataset.First;
for i:=0 to Vdataset.RecordCount-1 do
begin
rowval:=SouDataSet.fieldbyname(rowfield).AsString;
colval:=SouDataSet.fieldbyname(colfield).AsString;
dataval:=SouDataSet.fieldbyname(datafield).AsString;
if dataval='' then dataval:='0';
if DataSource.DataSet.Locate(rowfield,rowval,[loPartialKey]) then
begin
DataSource.DataSet.Edit;
DataSource.DataSet.FieldByName(colval).AsString:=dataval;
DataSource.DataSet.FieldByName('Sum').AsInteger:=
DataSource.DataSet.FieldByName('Sum').AsInteger+strtoint(dataval);
DataSource.DataSet.Post;
end
else
begin
DataSource.DataSet.Append;
DataSource.DataSet.FieldByName(rowfield).AsString:=rowval;
for j:=1 to DataSource.DataSet.Fields.Count-1 do
DataSource.DataSet.Fields[j].AsCurrency:=0;
DataSource.DataSet.FieldByName(colval).AsString:=dataval;
DataSource.DataSet.FieldByName('Sum').AsString:=dataval;
DataSource.DataSet.Post;
end;
Vdataset.Next;
end;
result:=DataSource.DataSet;
//生成交叉表数据集
tmpstrs.Free;
except
end;
end
else
showmessage('ColField Must be of Type String!') ;
end;
end;


以上代码在D7和SQL Server 7.0/2000测试通过

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
原表为
品种 货号 色号　 尺码 数量
　西服　xf01 1 165 2
西服　xf01 1 170 10
西服　xf01 1 175 8
西服　xf01 1 180 2
西服　xf01 2 165 5
西服　xf01 2 175 6

变为
品种 货号 色号　 165 170 175 180
西服　xf01 1 2 10 8 2
西服　xf01 2 5 6


select 品种,货号,色号,
sum(case when 尺码='165' then 数量 else null end) [165],
sum(case when 尺码='170' then 数量 else null end) [170],
sum(case when 尺码='175' then 数量 else null end) [175],
sum(case when 尺码='180' then 数量 else null end) [180]
from table1
group by 品种,货号,色号
order by 品种,货号,色号

我大致明白了
以前只寫普通的交叉表語句
感覺和我的問題不太一樣
結果看看複雜交叉的情況
應該是可以解決的
我正在嘗試
等解決了發佈結果並散分

仔細的看了上面的例子
其中Case 字段A when X then Y
這裡的X是寫死固定的
而我這裡的X是動態的,更具表裏實際數據的不同
來得出不同的結果,好像這些方法都不行啊

哦~有靈感了
不過方法很笨
程序裏去拚出SQL語句
事先先查詢出所有的X

这是这样，方法虽笨，却很实用，呵呵！

SELECT
SUM(CASE WHEN A=1 THEN B ELSE 0 END) 1,
SUM(CASE WHEN A=2 THEN B ELSE 0 END) 2,
....
FROM
TABLE_NAME

我的處理方法如下，
結果集：
字段名：BQTS_NUM Cost BookCost BQTS_PRC_PCT
10000 47.92 47.1 1.24
25000 46.8 46.0 1.20
50000 46.24 45.46 1.18
75000 44.56 43.82 1.12
語句：
select 4 LIN,
sum(case BQTS_NUM when 10000 then Cost else null end) [10000],
sum(case BQTS_NUM when 25000 then Cost else null end) [25000],
sum(case BQTS_NUM when 50000 then Cost else null end) [50000],
sum(case BQTS_NUM when 75000 then Cost else null end) [75000]
from vi_PSAmt
union
select 3 LIN,
sum(case BQTS_NUM when 10000 then BookCost else null end) [10000],
sum(case BQTS_NUM when 25000 then BookCost else null end) [25000],
sum(case BQTS_NUM when 50000 then BookCost else null end) [50000],
sum(case BQTS_NUM when 75000 then BookCost else null end) [75000]
from vi_PSAmt
union
select 2 LIN,
sum(case BQTS_NUM when 10000 then BQTS_PRC_PCT else null end) [10000],
sum(case BQTS_NUM when 25000 then BQTS_PRC_PCT else null end) [25000],
sum(case BQTS_NUM when 50000 then BQTS_PRC_PCT else null end) [50000],
sum(case BQTS_NUM when 75000 then BQTS_PRC_PCT else null end) [75000]
from vi_PSAmt
union
select 1 LIN,
sum(case BQTS_NUM when 10000 then BQTS_NUM else null end) [10000],
sum(case BQTS_NUM when 25000 then BQTS_NUM else null end) [25000],
sum(case BQTS_NUM when 50000 then BQTS_NUM else null end) [50000],
sum(case BQTS_NUM when 75000 then BQTS_NUM else null end) [75000]
from vi_PSAmt

最後結果：
字段名：LIN 10000 25000 50000 75000
1 10000 25000 50000 75000
2 1.24 1.20 1.18 1.12
3 47.10 46.0 45.46 43.82
4 47.92 46.80 46.24 44.56
其中LIN是爲了讓行按照我需要的結果排列
不知道大家能想到比這更好的辦法沒有

忘記說了，上面的SQL語句是程序中循環動態生成
因爲結果集不是固定的

CREATE PROCEDURE [Temp001] AS
Begin
Declare @sql varchar(8000)
Set @sql = 'select 1 as tmp,'

Select @sql = @sql + 'sum(case when BQTS_NUM='+cast(BQTS_NUM as varchar)+' then BQTS_NUM else 0 end) as '''+cast(BQTS_NUM as varchar)+''','
From Table1
Select @sql = left(@sql,len(@sql)-1)+' From Table1 Union Select 2,'

Select @sql = @sql + 'sum(case when BQTS_NUM='+cast(BQTS_NUM as varchar)+' then BQTS_PRC_PCT else 0 end) as '''+cast(BQTS_NUM as varchar)+''','
From Table1
Select @sql = left(@sql,len(@sql)-1)+' From Table1 Union Select 3,'

Select @sql = @sql + 'sum(case when BQTS_NUM='+cast(BQTS_NUM as varchar)+' then BookCost else 0 end) as '''+cast(BQTS_NUM as varchar)+''','
From Table1
Select @sql = left(@sql,len(@sql)-1)+' From Table1 Union Select 4,'

Select @sql = @sql + 'sum(case when BQTS_NUM='+cast(BQTS_NUM as varchar)+' then Cost else 0 end) as '''+cast(BQTS_NUM as varchar)+''','
From Table1
Select @sql = left(@sql,len(@sql)-1)+' From Table1 '

Exec(@sql)
End
GO

to QuickSilver:
果然是牛人啊，小弟佩服，正在研究你寫的procedure
顯然答案是對的
謝謝，發分~

將上面的hotboys大大提供的方法也放進來
方便自己察看：
動態交叉表範例
declare @sFName nvarchar(16),
@sqlText nvarchar(2000)

select @sqlText='select FItemID,'

select @sqlText=@sqltext+'(CASE FName WHEN '''+FName+''' THEN FAuxQty ELSE 0 END) AS ''' +FName+
''',' from (select distinct FName from test) as a

select @sqlText=left(@sqlText,Len(@sqlText)-1)+',FStockID,FDeptID from test'
print @sqltext
exec(@sqlText)
go

QuickSilver : 70
app2001 : 10
hotboys : 20

謝謝各位了！

>>to QuickSilver:
果然是牛人啊，小弟佩服，正在研究你寫的procedure
顯然答案是對的
謝謝，發分~
========================
我暈，人家拿我的改一下還得70分呀？關鍵是你自己要研究呀。

阿~你的我沒有仔細研究，先看了他的
然後回頭看你的......
Sorry