union select 的问题

Question

Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union Select vendorID, ''' + Name + ''' As emp, ' + Name + ' FROM [T] '
From SysColumns Where ID = OBJECT_ID('T') And Name Like 'Emp%' Order By ColID
Select @S = Stuff(@S, 1, 7, '') +' Order By emp'
EXEC(@S)

Answer 1

--放入一個存儲過程中
Create ProceDure SP_TEST
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union Select vendorID, ''' + Name + ''' As emp, ' + Name + ' FROM [T] '
From SysColumns Where ID = OBJECT_ID('T') And Name Like 'Emp%' Order By ColID
Select @S = Stuff(@S, 1, 7, '') +' Order By emp'
EXEC(@S)
End
GO
EXEC SP_TEST
GO

Answer 2

CREATE TABLE T (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GOINSERT INTO T  VALUES (1,4,3,5,4,4)
INSERT INTO T  VALUES (2,4,1,5,5,5)
INSERT INTO T  VALUES (3,4,3,5,4,4)
INSERT INTO T  VALUES (4,4,2,5,5,4)
INSERT INTO T  VALUES (5,5,1,5,5,5)select *
from t
unpivot
(orders for [EMP] in([Emp1],[Emp2],[Emp3],[Emp4],[Emp5]))as tbVendorID    orders      EMP
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
1           4           Emp1
1           3           Emp2
1           5           Emp3
1           4           Emp4
1           4           Emp5
2           4           Emp1
2           1           Emp2
2           5           Emp3
2           5           Emp4
2           5           Emp5
3           4           Emp1
3           3           Emp2
3           5           Emp3
3           4           Emp4
3           4           Emp5
4           4           Emp1
4           2           Emp2
4           5           Emp3
4           5           Emp4
4           4           Emp5
5           5           Emp1
5           1           Emp2
5           5           Emp3
5           5           Emp4
5           5           Emp5(25 行受影响)

Answer 3

2005：
select *
from t
unpivot
(orders for [EMP] in([Emp1],[Emp2],[Emp3],[Emp4],[Emp5]))as tb
order by EMP
VendorID    orders      EMP
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
1           4           Emp1
2           4           Emp1
3           4           Emp1
4           4           Emp1
5           5           Emp1
1           4           Emp1
2           4           Emp1
3           4           Emp1
4           4           Emp1
5           5           Emp1
5           1           Emp2
4           2           Emp2
3           3           Emp2
2           1           Emp2
1           3           Emp2
5           1           Emp2
4           2           Emp2
3           3           Emp2
2           1           Emp2
1           3           Emp2
1           5           Emp3
2           5           Emp3
3           5           Emp3
4           5           Emp3
5           5           Emp3
1           5           Emp3
2           5           Emp3
3           5           Emp3
4           5           Emp3
5           5           Emp3
5           5           Emp4
4           5           Emp4
3           4           Emp4
2           5           Emp4
1           4           Emp4
5           5           Emp4
4           5           Emp4
3           4           Emp4
2           5           Emp4
1           4           Emp4
1           4           Emp5
2           5           Emp5
3           4           Emp5
4           4           Emp5
5           5           Emp5
1           4           Emp5
2           5           Emp5
3           4           Emp5
4           4           Emp5
5           5           Emp5(50 行受影响)

Answer 4

Create ProceDure SP_TEST
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union Select vendorID, ''' + Name + ''' As emp, ' + Name + ' FROM [T] '
From SysColumns Where ID = OBJECT_ID('T') And Name Like 'Emp%' Order By ColID
Select @S = Stuff(@S, 1, 7, '') +' Order By emp'
EXEC(@S)
End
GO
EXEC SP_TEST
GO
如果列名没有规律的话 Emp, xx, xi4,...，是不是就没有办法了。
是否可以写出通用的存储过程，把所有的列都unpivot

Answer 5

如果列名没有规律的话 Emp, xx, xi4,...，是不是就没有办法了。
是否可以写出通用的存储过程，把所有的列都unpivot-----------
如果列名沒有規律，可以這麼寫。就是得到除了VendorID 之外的所有列.
--放入一個存儲過程中
Create ProceDure SP_TEST
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union Select vendorID, ''' + Name + ''' As emp, ' + Name + ' FROM [T] '
From SysColumns Where ID = OBJECT_ID('T') And Name != 'VendorID' Order By ColID
Select @S = Stuff(@S, 1, 7, '') +' Order By emp'
EXEC(@S)
End
GO
EXEC SP_TEST

Answer 6

太好了！
正是想要的，我觉得这个procedure 还是很有用的。在加上下面我收集的povit，就是一对绝配了。（用2005的话要修改一下，2005有新的关键字povit）。
CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
ASDECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFFEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivotDROP TABLE ##pivotSELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')EXEC (@select)
SET ANSI_WARNINGS ON