复杂的查询

Question

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-05 16:02:52
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (Name VARCHAR(2),Amount1 INT,Amount2 INT)
INSERT INTO @T
SELECT 'A1',100,90 UNION ALL
SELECT 'A1',100,80 UNION ALL
SELECT 'A2',90,90 UNION ALL
SELECT 'A2',90,80 UNION ALL
SELECT 'A3',100,80 UNION ALL
SELECT 'A3',100,80 UNION ALL
SELECT 'A3',100,80--SQL查询如下:SELECT *
FROM @T AS A
    UNPIVOT(Amount FOR Amount_Name IN([Amount1],[Amount2])) AS unpvt
    PIVOT(SUM(Amount) FOR Name IN([A1],[A2],[A3])) AS pvt/*
Amount_Name              A1          A2          A3
---------------------- ----------- ----------- -----------
Amount1                   200         180         300
Amount2                   170         170         240(2 行受影响)
*/

Answer 1

select 'AMOUNT1',
(select SUM(amount1) from tb where name='A1') as A1,
(select SUM(amount1) from tb where name='A2') as A2,
(select SUM(amount1) from tb where name='A3') as A3
from tb 
union all
select 'AMOUNT2',
(select SUM(amount2) from tb where name='A1') as A1,
(select SUM(amount2) from tb where name='A2') as A2,
(select SUM(amount2) from tb where name='A3') as A3
from tb 

Answer 2

create  TABLE tb (Name VARCHAR(2),Amount1 INT,Amount2 INT)
INSERT INTO tb 
SELECT 'A1',100,90 UNION ALL
SELECT 'A1',100,80 UNION ALL
SELECT 'A2',90,90 UNION ALL
SELECT 'A2',90,80 UNION ALL
SELECT 'A3',100,80 UNION ALL
SELECT 'A3',100,80 UNION ALL
SELECT 'A3',100,80
go
 
select distinct 'AMOUNT1',
(select SUM(amount1) from tb where name='A1') as A1,
(select SUM(amount1) from tb where name='A2') as A2,
(select SUM(amount1) from tb where name='A3') as A3
from tb 
union all
select distinct 'AMOUNT2',
(select SUM(amount2) from tb where name='A1') as A1,
(select SUM(amount2) from tb where name='A2') as A2,
(select SUM(amount2) from tb where name='A3') as A3
from tb 
/*
AMOUNT1 200 180 300
AMOUNT2 170 170 240
*/

Answer 3

select 'Amount1'
,[A1]=max(case when [Name]='A1' then [Amount1] else 0 end),
[A2]=max(case when [Name]='A2' then [Amount1] else 0 end),
[A3]=max(case when [Name]='A3' then [Amount1] else 0 end) from tab
union all select 'Amount2'
,[A1]=max(case when [Name]='A1' then [Amount2] else 0 end),
[A2]=max(case when [Name]='A2' then [Amount2] else 0 end),
[A3]=max(case when [Name]='A3' then [Amount2] else 0 end) from tab

Answer 4

列转化成行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]GO/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78(2 行受影响)
*/行转化成列
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]go/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78(8 行受影响)
*/