field1 field2
B 2004-12-14
D 2004-12-16
F 2004-12-18
K 2004-12-20
L 2004-12-21
想要變成
B D F K L
2004-12-14 2004-12-16 2004-12-18 2004-12-20 2004-12-21
謝謝大家
...全文
1034打赏收藏
怎么把表格中的內容和字段名交換﹐用SQL 語句﹐說不清楚﹐請看
現表內容 field1 field2 B2004-12-14 D2004-12-16 F2004-12-18 K2004-12-20 L2004-12-21 想要變成 B D F K L 2004-12-14 2004-12-16 2004-12-18 2004-12-20 2004-12-21 謝謝大家
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 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 都计算与本例显示相同的信息种类,但格式稍有不同。
--建立测试环境
create table table1 (field1 varchar(10),field2 datetime)
insert into table1 select 'B','2004-12-14'
union all select 'D','2004-12-16'
union all select 'F','2004-12-18'
union all select 'K','2004-12-20'
union all select 'L','2004-12-21'
--执行动态查询
Declare @S Nvarchar(4000)
select @s='Select '
Select @S=@S+' max(Case When field1=N'''+field1 +''' Then field2 Else NULL End ) As '+ quotename(field1)+','
from (select field1 from table1 ) D
set @s=left(@s,len(@s)-1)
Select @S=@S+' from table1 '
EXEC(@S)
--查询结果
B D F K L
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2004-12-14 00:00:00.000 2004-12-16 00:00:00.000 2004-12-18 00:00:00.000 2004-12-20 00:00:00.000 2004-12-21 00:00:00.000