关于动态取列然后行列转换的问题

Question

我将要从一个表里查询出有多少列，并且行列转换后，再拿结果去和其他表做查询，能不能实现，最好给个例子，多谢各位大侠了。分不多，请多多帮忙了。

Answer 1

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------

Answer 2

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (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
--2000方法:
动态: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
动态:declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select * 
from 
Class 
pivot 
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态: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+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态: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),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
Class 
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select 
[Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
 
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--2000:动态: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
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
Student,[Course],[Score] 
from 
Class 
unpivot 
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78(8 行受影响)
*/

Answer 3

行列转换参考一二楼,看一个表有多少列:select count(1) from syscolumns where id=object_id('表名')

Answer 4

标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------

Answer 5

CREATE      PROC PROC_PM_ManyPriceChange
(@StoreCD INT, @ProductCD VARCHAR(13), @NewPrice INT, @StartDate VARCHAR(10), @EndDate VARCHAR(10),  @SStoreCD VARCHAR(1000), @CStoreCD INT)
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @ST TABLE(StoreCD INT, StoreName VARCHAR(100))
INSERT @ST
SELECT DISTINCT TOP 10
CS.StoreCD AS CStoreCD
  ,CS.StoreName
FROM
dbo.RelationStore RS
JOIN
dbo.CompareStore CS
ON
CS.StoreCD = RS.CStoreCD
WHERE
RS.SStoreCD IN (SELECT StoreCD FROM DBO.STRINGSPLIT(@SStoreCD, ','))
AND
CS.StoreCD = ISNULL(NULLIF(@CStoreCD, 0), CS.StoreCD)
ORDER BY
CS.StoreCD
SELECT * FROM @ST

SELECT @SQL = 'DECLARE @TB TABLE(StoreCD INT, ProductCD VARCHAR(13), NewPrice INT, SalesStart VARCHAR(10), SalesEnd VARCHAR(10))
INSERT @TB SELECT ' + CAST(@StoreCD AS VARCHAR(4))  + ' , ''' + CAST(@ProductCD AS VARCHAR(13)) + ''', ' + CAST(@NewPrice AS VARCHAR(6)) + ' , ''' + ISNULL(@StartDate,'') + ''',  ''' + ISNULL(@EndDate, '') + ''''

SELECT @SQL = @SQL + CHAR(13) + ' UNION ALL 
SELECT ' + CAST(@StoreCD AS VARCHAR(4)) + ' AS StoreCD, A.ProductCD, CAST(' + CAST(@NewPrice AS VARCHAR(6)) + ' * A.BundleQty * 1.00 / B.BundleQty AS INT) AS NewPrice' +  ' , ''' + ISNULL(@StartDate,'') + ''' AS StartDate,  ''' + ISNULL(@EndDate, '') + '''  AS EndDate  FROM dbo.Bundles A LEFT OUTER JOIN dbo.Bundles B ON A.BundleID = B.ProductCD WHERE A.BundleID <> A.ProductCD AND A.BundleID = ''' + @ProductCD + ''''

SELECT @SQL1 =ISNULL(@SQL1,'') +  ',ISNULL(NULLIF(SUM(CASE WHEN CS.StoreCD = ' + CAST(T.StoreCD AS VARCHAR(4)) + ' THEN CAST(ISNULL(CPP.SalesPrice,0) AS INT)'  + ' ELSE 0 END), 0), '''')  AS '  + T.StoreName+CAST(T.StoreCD AS VARCHAR(4)) +  CHAR(13)
FROM
@ST T
SELECT @SQL =@SQL+ CHAR(13) +  '
SELECT
TB.StoreCD
,TB.ProductCD
' + @SQL1 + '
INTO #T1
FROM
@TB TB
LEFT OUTER JOIN
dbo.RelationStore RS
ON
RS.SStoreCD = TB.StoreCD
LEFT OUTER JOIN
dbo.CompareStore CS
ON
CS.StoreCD = RS.CStoreCD
LEFT OUTER JOIN
dbo.ComparedProductPrice CPP
ON
CS.StoreCD = CPP.StoreCD
AND
TB.ProductCD = CPP.ProductCD
LEFT OUTER JOIN
MasterDB.dbo.PurchaseGroupBranches PGB
ON
TB.StoreCD = PGB.BranchCD
LEFT OUTER JOIN
MasterDB.dbo.PurchaseGroupProducts PGP
ON
PGP.PurchaseGroupCD = PGB.PurchaseGroupCD
AND
PGP.ProductCD = TB.ProductCD
LEFT OUTER JOIN
MasterDB.dbo.vAllBranchSalesInfo BSI
ON
TB.StoreCD = BSI.BranchCD
AND
TB.ProductCD = BSI.ProductCD
AND
BSI.POSSalesPrice > 0
GROUP BY
TB.StoreCD
,TB.ProductCD'
SELECT  @SQL = @SQL +CHAR(13) +  '
SELECT DISTINCT
CAST(TB.StoreCD AS VARCHAR(5)) + ''' + '-' + ''' + B.BranchName AS StoreCD
,TB.ProductCD
,MP.ProductName
,MP.SpecName
,MP.BrandName
,CAST(PGP.CostPrice AS INT)  AS CostPrice
,CAST(PP.StandardPrice AS INT) AS StandardPrice
,CAST(PP.LowerLimitedPrice AS INT) AS LowerLimitedPrice
,CAST(BSI.POSSalesPrice AS INT) AS POSSalesPrice
,TB.NewPrice
,TB.SalesStart
  ,TB.SalesEnd
,S.*
FROM
@TB TB
JOIN
MasterDB.dbo.Branches B
ON
TB.StoreCD = B.BranchCD
LEFT OUTER JOIN
dbo.ProductsPrice PP
ON
PP.ProductCD = TB.ProductCD
AND
PP.BusinessTalks_Type_CD = (SELECT TOP 1
BusinessTalks_Type_CD
FROM 
dbo.ProductsPrice 
WHERE 
ProductCD = TB.ProductCD
AND
BusinessTalks_Type_CD IN (1, 3)
ORDER BY
BusinessTalks_Type_CD DESC)
LEFT OUTER JOIN
MasterDB.dbo.Products MP
ON
MP.ProductCD = TB.ProductCD
LEFT OUTER JOIN
MasterDB.dbo.PurchaseGroupBranches PGB
ON
TB.StoreCD = PGB.BranchCD
LEFT OUTER JOIN
MasterDB.dbo.PurchaseGroupProducts PGP
ON
PGP.PurchaseGroupCD = PGB.PurchaseGroupCD
AND
PGP.ProductCD = TB.ProductCD
LEFT OUTER JOIN
MasterDB.dbo.vAllBranchSalesInfo BSI
ON
TB.StoreCD = BSI.BranchCD
AND
TB.ProductCD = BSI.ProductCD
AND
BSI.POSSalesPrice > 0
JOIN
#T1 S
ON
TB.StoreCD = S.StoreCD
AND
TB.ProductCD = S.ProductCD' EXEC (@SQL)

END这是我最近写的一个，大体能实现楼主的要求，您看看吧