有没有在SQL Server调换行列的任何设施（有可能在MS-访问）？ 我昏昏沉沉，因为这个工厂是可以在MS-访问权限，但不能在SQL Server中。 它是由设计，此功能还没有被包括在SQL Server？
Answer 1:
在这个例子http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm只有当你事先知道行值可以是什么在起作用。 例如，假设你有一个自定义属性和自定义属性的实体作为一个子表，其中子表基本上是变量/值对的行实现，而这些变量/值对是可配置的。
color red
size big
city Chicago
我将描述一个可行的技术。 我用它。 我不促进它，但它的作品。
转动，你不知道这个值可以是事先什么数据，就没有列飞创建临时表。 然后使用游标来遍历你行，发卡每个变量动态构建“ALTER TABLE”，从而使最终的临时表有列，颜色，大小城市。
然后你在临时表中插入一行，通过通过变量值对另一个游标更新它，然后选择它，通常与其父实体加盟，实际上使得它看起来像那些自定义变量/值对都像内置在原来的母公司列。
Answer 2:
所描述的方法光标可能是至少SQL状使用。 如前所述，SQL 2005和具有PIVOT伟大的工程。 但对于旧版本和非MS SQL服务器，从“Optimzing的Transact-SQL”（编辑Rozenshtein方法：绝版，但无济于事从亚马逊： http://www.amazon.com/Optimizing-Transact-SQL-高级编程的技术/ DP / 0964981203 ），是极好的枢转和unpivoting数据。 它使用点特性打开基于行的数据成列。 Rozenshtein介绍几种情况下，这里有一个例子：
SELECT
RowValueNowAColumn =
CONVERT(varchar,
MAX(
SUBSTRING(myTable.MyVarCharColumn,1,DATALENGTH(myTable.MyVarCharColumn)
* CHARINDEX(sa.SearchAttributeName,'MyRowValue'))))
FROM
myTable
这种方法是很多比使用情况报表和作品的各种数据类型和SQL实现（而不仅仅是MS SQL）的效率更高。
Answer 3:
最好限制在小规模的这样的事情。 如果您使用SQL 2K，但并没有PIVOT功能可用，我起草了一个存储过程应该为你做的工作。 误事赶工的位，所以把它扯断，就像你喜欢。 粘贴下面到SQL窗口，并在底部编辑EXEC为优选的。 如果你想看到真实生成的内容，去掉中间的--s：
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'USP_LIST_CONCAT')
DROP PROCEDURE USP_LIST_CONCAT
GO
CREATE PROCEDURE USP_LIST_CONCAT (@SourceTable NVARCHAR(1000) = '' ,@SplitColumn NVARCHAR(1000) = '' , @Deli NVARCHAR(10) = '', @KeyColumns NVARCHAR(2000) = '' , @Condition NVARCHAR(1000) = '')
AS
BEGIN
SET NOCOUNT ON
/* PROCEDURE CREATED 2010 FOR SQL SERVER 2000. SIMON HUGHES. */
/* NOTES: REMOVE --'s BELOW TO LIST GENERATED SQL. */
IF @SourceTable = '' OR @SourceTable = '?' OR @SourceTable = '/?' OR @SplitColumn = '' OR @KeyColumns = ''
BEGIN
PRINT 'Format for use:'
PRINT ' USP_LIST_CONCAT ''SourceTable'', ''SplitColumn'', ''Deli'', ''KeyColumn1,...'', ''Column1 = 12345 AND ...'''
PRINT ''
PRINT 'Description:'
PRINT 'The SourceTable should contain a number of records acting as a list of values.'
PRINT 'The SplitColumn should be the name of the column holding the values wanted.'
PRINT 'The Delimiter may be any single character or string ie ''/'''
PRINT 'The KeyColumn may contain a comma separated list of columns that will be returned before the concatenated list.'
PRINT 'The optional Conditions may be left blank or may include the following as examples:'
PRINT ' ''Column1 = 12334 AND (Column2 = ''ABC'' OR Column3 = ''DEF'')'''
PRINT ''
PRINT 'A standard list in the format:'
PRINT ' Store1, Employee1, Rabbits'
PRINT ' Store1, Employee1, Dogs'
PRINT ' Store1, Employee1, Cats'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'Will be returned as:'
PRINT ' Store1, Employee1, Cats/Dogs/Rabbits'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'A full ORDER BY and DISTINCT is included'
RETURN -1
END
DECLARE @SQLStatement NVARCHAR(4000)
SELECT @SQLStatement = '
DECLARE @DynamicSQLStatement NVARCHAR(4000)
SELECT @DynamicSQLStatement = ''SELECT '+@KeyColumns+', SUBSTRING(''
SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' + '' + CHAR(10) +
'' MAX(CASE WHEN '+@SplitColumn+' = ''''''+RTRIM('+@SplitColumn+')+'''''' THEN '''''+@Deli+'''+RTRIM('+@SplitColumn+')+'''''' ELSE '''''''' END)''
FROM '+ @SourceTable +' ORDER BY '+@SplitColumn+'
SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' ,2,7999) List'' + CHAR(10) + ''FROM '+ @SourceTable+''' + CHAR(10) +'''+CASE WHEN @Condition = '' THEN '/* WHERE */' ELSE 'WHERE '+@Condition END+ '''+ CHAR(10) + ''GROUP BY '+@KeyColumns+'''
SELECT @DynamicSQLStatement = REPLACE(@DynamicSQLStatement,''( +'',''('')
-- SELECT @DynamicSQLStatement -- DEBUG ONLY
EXEC (@DynamicSQLStatement)'
EXEC (@SQLStatement)
END
GO
EXEC USP_LIST_CONCAT 'MyTableName', 'ColumnForListing', 'Delimiter', 'KeyCol1, KeyCol2', 'Column1 = 123456'
Answer 4:
在SQL Server 2005 UNPIVOT，我找到了一个好文章
列到行式-SQL服务器
Answer 5:
我有以下格式数据
Survey_question_ID
电子邮件（用户 ）
回答
1次调查有13个问题和答案所需的输出我想要的是
用户 --- Survey_question_ID1 --- Survey_question_ID2
电子邮件 --- 答案 --- 答案 ........ 等等
下面是SQL Server 2000的解决方案，原因字段数据类型为文本 。
DROP TABLE #tmp
DECLARE @tmpTable TABLE
(
emailno NUMERIC,
question1 VARCHAR(80),
question2 VARCHAR(80),
question3 VARCHAR(80),
question4 VARCHAR(80),
question5 VARCHAR(80),
question6 VARCHAR(80),
question7 VARCHAR(80),
question8 VARCHAR(80),
question9 VARCHAR(80),
question10 VARCHAR(80),
question11 VARCHAR(80),
question12 VARCHAR(80),
question13 VARCHAR(8000)
)
DECLARE @tmpTable2 TABLE
(
emailNumber NUMERIC
)
DECLARE @counter INT
DECLARE @Email INT
SELECT @counter =COUNT(DISTINCT ans.email) FROM answers ans WHERE ans.surveyname=100430 AND ans.qnpkey BETWEEN 233702 AND 233714
SELECT * INTO #tmp FROM @tmpTable
INSERT INTO @tmpTable2 (emailNumber) SELECT DISTINCT CAST(ans.email AS NUMERIC) FROM answers ans WHERE ans.surveyname=100430 AND ans.qnpkey BETWEEN 233702 AND 233714
WHILE @counter >0
BEGIN
SELECT TOP 1 @Email= emailNumber FROM @tmpTable2
INSERT INTO @tmpTable (emailno) VALUES (@Email )
Update @tmpTable set question1=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233702 and ans.email=@Email
Update @tmpTable set question2=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233703 and email=@email
Update @tmpTable set question3=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233704 and email=@email
Update @tmpTable set question4=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233705 and email=@email
Update @tmpTable set question5=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233706 and email=@email
Update @tmpTable set question6=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233707 and email=@email
Update @tmpTable set question7=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233708 and email=@email
Update @tmpTable set question8=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233709 and email=@email
Update @tmpTable set question9=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233710 and email=@email
Update @tmpTable set question10=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233711 and email=@email
Update @tmpTable set question11=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233712 and email=@email
Update @tmpTable set question12=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233713 and email=@email
Update @tmpTable set question13=CAST(answer as VARCHAR(8000)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233714 and email=@email
insert into #tmp select * from @tmpTable
DELETE FROM @tmpTable
DELETE FROM @tmpTable2 WHERE emailNumber= @Email
set @counter =@counter -1
End
select * from #tmp
文章来源: Transpose a set of rows as columns in SQL Server 2000
