表列数据作为sql中的其他表列名 [英] table column data as other table column name in sql
问题描述
大家好,
i希望从其他表格列数据中分配表格列名。
场景:
我有一张桌子,Departments,其中我有5个depts
n
其他桌子单位,每个部门都有自己的单位。
现在,我希望这些单位名称作为表格列名。
i意思是,我将创建一个临时表,其列名可以是单位名称。
存储过程或任何其他方式。
我可以这样做,是否有可能。
Hi guys,
i want to assign table column name from other table column data.
Scenario:
I have one table, Departments, in which i have 5 depts
n
other table units, each dept has its own units.
Now, i want these unit names as table column names.
i mean, i'll create one temp table whose column names could be the units names.
In stored procedure or any other way.
Can i do this, is it possible.
In SQL-SERVER-2008-R2
任何人都可以帮助我。
谢谢
can anyone please help me.
Thanks
推荐答案
看看SQL Server中的PIVOT运算符:
http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx [ ^ ]
Have a look at the PIVOT operator in SQL Server:
http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx[^]
查看以下文章。
http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server [ ^ ]
在上面的文章中,明确定义了如何将你的行转换成列,如果你的行(你的情况下单位)是修复或不修复。
请接受解决方案,如果它解决了您的问题。
谢谢
Check out below Articles.
http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server[^]
In the above article, it is clearly defined that how you can convert your Rows into columns, if your Rows(Unit in your case) is fix or not fix.
Please accept the solution, if it is solving your problem.
Thanks
在下面尝试一下,可能会解决你的问题。
************** ***主要*********************开始
try it with below,it might be sol your problem.
*****************Main *********************start
DECLARE @SEL NVARCHAR(MAX)
SET @SEL='select
0 unit_id from Unit_table
group by unit_name
'
exec dynamic_pivot @SEL
,'unit_name'
,'AVG(unit_id )'
print @SEL
select * into #temp1 from ##tmp213
select * from #temp1
DROP TABLE ##tmp213
DROP TABLE#temp1
*****************主要****** ***************结束
首先执行此
创建程序dynamic_pivot
(
@select varchar(2000),
@PototCol varchar(100),
@Summaries varchar(100)
- 在PIVOT COLUMN中获取数据
)as
声明@pivot varchar(max),@ sql varchar(max)
select @select = replace(@select ,'选择','选择'+ @ PivotCol +'为
pivot_col,')
< br $>
创建表#pivot_columns(pivot_column varchar(100))
选择@ sql ='从('+ @ select +')选择distinct pivot_col为t'
插入#pivot_columns
exec(@sql)
选择@ pivot = coalesce(@pivot +',','')+'['+ pivot_column +']'来自
#pivot_columns
select @ sql =
'
选择* INTO ## tmp213来自
(
'+ @ select +'
)as t
pivot
(
'+ @ Summaries +'for pivot_col in('+ @ pivot +')
)as p
'
exec(@sql)
DROP TABLE #pivot_columns
DROP TABLE #temp1
*****************Main *********************End
First Execute this
CREATE procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
-- GET DATA IN PIVOT COLUMN
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as
pivot_col,')
create table #pivot_columns (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'
insert into #pivot_columns
exec(@sql)
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from
#pivot_columns
select @sql=
'
select * INTO ##tmp213 from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)
DROP TABLE #pivot_columns
这篇关于表列数据作为sql中的其他表列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!