blocks|key|3147972|text|我找到的唯一方法就是使用动态SQL并将列标签放入一个变量中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3147973|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The only way I've found to do this is to use dynamic SQL and put the column labels into a variable.

blocks|key|359473|text|您可以查询information_schema以获得列名和类型，然后在构建结果集时将结果用作子查询。注您可能需要对登录的访问权限进行一些更改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|359474|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

you could query information_schema to get the column names and types, then use the result as a subquery when you build your result set. Note you'll likely have to change the login's access a bit.

blocks|key|362817|text|我知道你说没有动态SQL，但我看不到在直接的SQL中有任何方法可以做到这一点。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|362818|如果你看看我在Pivot+Table+and+Concatenate+Columns和PIVOT+in+sql+2005上对类似问题的回答|362819|那里的动态SQL不容易受到注入的攻击，也没有充分的理由禁止它。另一种可能性(如果数据很少更改)是代码生成，而不是动态SQL，而是定期向存储过程生成SQL。|362820|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns-sql-problem#159803|1|https://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763^0|9|3|M|3|0|7|Z|0|17|H|1|0|5|3|1M|3|21|3|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|H|3|I|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]]]

I know you said no dynamic <code>SQL</code>, but I don't see any way to do it in straight <code>SQL</code>.

If you check out my answers to similar problems at <a href="https://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns-sql-problem#159803">Pivot Table and Concatenate Columns</a> and <a href="https://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763">PIVOT in sql 2005</a>

The dynamic <code>SQL</code> there is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic <code>SQL</code>, the <code>SQL</code> is generated to a stored procedure on a regular basis.

blocks|key|3148028|text|这与PIVOT+in+sql+2005相同|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3148029|如果要在报告中使用此数据，则可以使用SSRS矩阵。它将从结果集动态生成列。我已经用过很多次了--它在动态交叉表报表中工作得很好。|3148030|下面是一个使用动态sql的很好的例子。http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx|3148031|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/198716/pivot-in-sql-2005#198879|1|http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx^0|2|H|0|0|0|J|27|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

This is the same as <a href="https://stackoverflow.com/questions/198716/pivot-in-sql-2005#198879">PIVOT in sql 2005</a>

If this data is for consumption in a report you could use a SSRS matrix. It will generate columns dynamically from result set. I've used it many times - it works quite well for dynamic crosstab reports.

Here's a good example w/ dynamic sql. 
<a href="http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx" rel="nofollow noreferrer">http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx</a>

blocks|key|3148033|text|select+studentname,[Assign1],[Assign2],[Assign3],[Total]+
from+
(
+select+studentname,+assignname,+grade+from+student
)s
pivot(sum(Grade)+for+assignname+IN([Assign1],[Assign2],[Assign3],[Total]))+as+pvt|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3148034|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select studentname,[Assign1],[Assign2],[Assign3],[Total] 
from 
(
 select studentname, assignname, grade from student
)s
pivot(sum(Grade) for assignname IN([Assign1],[Assign2],[Assign3],[Total])) as pvt
</code></pre>

blocks|key|360245|text|SELECT+TrnType
INTO+#Temp1
FROM
(
++++SELECT+'['+%2B+CAST(TransactionType+AS+VARCHAR(4))+%2B+']'+AS+TrnType+FROM+tblPaymentTransactionTypes
)+AS+tbl1

SELECT+*+FROM+#Temp1

SELECT+*+FROM
(
++++SELECT+FirstName+%2B+'+'+%2B+LastName+AS+Patient,+TransactionType,+ISNULL(PostedAmount,+0)+AS+PostedAmount
++++FROM+tblPaymentTransactions
++++++++++++INNER+JOIN+emr_PatientDetails+ON+tblPaymentTransactions.PracticeID+=+emr_PatientDetails.PracticeId
++++++++++++INNER+JOIN+tblPaymentTransactionDetails+ON+emr_PatientDetails.PatientId+=+tblPaymentTransactionDetails.PatientID
++++++++++++++++++++++++AND+tblPaymentTransactions.TransactionID+=+tblPaymentTransactionDetails.TransactionID
++++WHERE+emr_PatientDetails.PracticeID+=+152
)+tbl
PIVOT+(SUM(PostedAmount)+FOR+[TransactionType]+IN+(SELECT+*+FROM+#Temp1)
)+AS+tbl4|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|360246|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT TrnType
INTO #Temp1
FROM
(
 SELECT '[' + CAST(TransactionType AS VARCHAR(4)) + ']' AS TrnType FROM tblPaymentTransactionTypes
) AS tbl1

SELECT * FROM #Temp1

SELECT * FROM
(
 SELECT FirstName + ' ' + LastName AS Patient, TransactionType, ISNULL(PostedAmount, 0) AS PostedAmount
 FROM tblPaymentTransactions
 INNER JOIN emr_PatientDetails ON tblPaymentTransactions.PracticeID = emr_PatientDetails.PracticeId
 INNER JOIN tblPaymentTransactionDetails ON emr_PatientDetails.PatientId = tblPaymentTransactionDetails.PatientID
 AND tblPaymentTransactions.TransactionID = tblPaymentTransactionDetails.TransactionID
 WHERE emr_PatientDetails.PracticeID = 152
) tbl
PIVOT (SUM(PostedAmount) FOR [TransactionType] IN (SELECT * FROM #Temp1)
) AS tbl4
</code></pre>

blocks|key|358134|text|要使用动态sql对此数据执行PIVOT操作，您可以在SQL+Server+2005%2B中使用以下代码：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|358135|创建表：|BOLD|358136|CREATE+TABLE+yourtable
++++([StudentName]+varchar(8),+[AssignmentName]+varchar(12),+[Grade]+int)
;

INSERT+INTO+yourtable
++++([StudentName],+[AssignmentName],+[Grade])
VALUES
++++('StudentA',+'Assignment+1',+100),
++++('StudentA',+'Assignment+2',+80),
++++('StudentA',+'Total',+180),
++++('StudentB',+'Assignment+1',+100),
++++('StudentB',+'Assignment+2',+80),
++++('StudentB',+'Assignment+3',+100),
++++('StudentB',+'Total',+280)
;|code-block|syntax|javascript|358137|Dynamic+PIVOT:|358138|DECLARE+@cols+AS+NVARCHAR(MAX),
++++@query++AS+NVARCHAR(MAX)

select+@cols+=+STUFF((SELECT+distinct+','+%2B+QUOTENAME(AssignmentName)+
++++++++++++++++++++from+yourtable
++++++++++++FOR+XML+PATH(''),+TYPE
++++++++++++).value('.',+'NVARCHAR(MAX)')+
++++++++,1,1,'')

set+@query+=+'SELECT+StudentName,+'+%2B+@cols+%2B+'+from+
+++++++++++++(
++++++++++++++++select+StudentName,+AssignmentName,+grade
++++++++++++++++from+yourtable
++++++++++++)+x
++++++++++++pivot+
++++++++++++(
++++++++++++++++min(grade)
++++++++++++++++for+assignmentname+in+('+%2B+@cols+%2B+')
++++++++++++)+p+'

execute(@query)|358139|请参阅SQL+Fiddle+with+Demo|358140|结果是：|358141|%7C+STUDENTNAME+%7C+ASSIGNMENT+1+%7C+ASSIGNMENT+2+%7C+ASSIGNMENT+3+%7C+TOTAL+%7C
--------------------------------------------------------------------
%7C++++StudentA+%7C++++++++++100+%7C+++++++++++80+%7C+++++++(null)+%7C+++180+%7C
%7C++++StudentB+%7C++++++++++100+%7C+++++++++++80+%7C++++++++++100+%7C+++280+%7C|358142|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/42b01/2^0|E|5|0|0|4|0|0|0|E|0|0|3|K|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|18|8|@$9|19|A|1A|B|H]]|D|@]|E|$]]|$1|I|3|J|5|K|7|1B|8|@]|D|@]|E|$L|M]]|$1|N|3|O|5|6|7|1C|8|@$9|1D|A|1E|B|H]]|D|@]|E|$]]|$1|P|3|Q|5|K|7|1F|8|@]|D|@]|E|$L|M]]|$1|R|3|S|5|6|7|1G|8|@]|D|@$9|1H|A|1I|1|1J]]|E|$]]|$1|T|3|U|5|6|7|1K|8|@]|D|@]|E|$]]|$1|V|3|W|5|K|7|1L|8|@]|D|@]|E|$L|M]]|$1|X|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|Y|$Z|$5|10|11|12|E|$13|14]]]]

To <code>PIVOT</code> this data using dynamic sql you can use the following code in SQL Server 2005+:

Create Table:

<pre><code>CREATE TABLE yourtable
 ([StudentName] varchar(8), [AssignmentName] varchar(12), [Grade] int)
;

INSERT INTO yourtable
 ([StudentName], [AssignmentName], [Grade])
VALUES
 ('StudentA', 'Assignment 1', 100),
 ('StudentA', 'Assignment 2', 80),
 ('StudentA', 'Total', 180),
 ('StudentB', 'Assignment 1', 100),
 ('StudentB', 'Assignment 2', 80),
 ('StudentB', 'Assignment 3', 100),
 ('StudentB', 'Total', 280)
;
</code></pre>

Dynamic PIVOT:

<pre><code>DECLARE @cols AS NVARCHAR(MAX),
 @query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AssignmentName) 
 from yourtable
 FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)') 
 ,1,1,'')

set @query = 'SELECT StudentName, ' + @cols + ' from 
 (
 select StudentName, AssignmentName, grade
 from yourtable
 ) x
 pivot 
 (
 min(grade)
 for assignmentname in (' + @cols + ')
 ) p '

execute(@query)
</code></pre>

See <a href="http://sqlfiddle.com/#!3/42b01/2" rel="noreferrer">SQL Fiddle with Demo</a>

The result is:

<pre><code>| STUDENTNAME | ASSIGNMENT 1 | ASSIGNMENT 2 | ASSIGNMENT 3 | TOTAL |
--------------------------------------------------------------------
| StudentA | 100 | 80 | (null) | 180 |
| StudentB | 100 | 80 | 100 | 280 |
</code></pre>

I am working with a set of data that looks something like the following.

<blockquote>
<pre><code>StudentName | AssignmentName | Grade
---------------------------------------
StudentA | Assignment 1 | 100
StudentA | Assignment 2 | 80
StudentA | Total | 180
StudentB | Assignment 1 | 100
StudentB | Assignment 2 | 80
StudentB | Assignment 3 | 100
StudentB | Total | 280
</code></pre>
</blockquote>

The name and number of assignments are dynamic, I need to get results simlilar to the following.

<blockquote>
<pre><code>Student | Assignment 1 | Assignment 2 | Assignment 3 | Total
--------------------------------------------------------------------
Student A | 100 | 80 | null | 180
Student B | 100 | 80 | 100 | 280
</code></pre>
</blockquote>

Now ideally I would like to sort the column based on a "due date" that could be included/associated with each assignment. The total should be at the end if possible (It can be calculated and removed from the query if possible.)

I know how to do it for the 3 assignments using pivot with simply naming the columns, it is trying to do it in a dynamic fashion that I haven't found a GOOD solution for yet. I am trying to do this on SQL Server 2005

EDIT

Ideally I would like to implement this WITHOUT using Dynamic SQL, as that is against the policy. If it isn't possible...then a working example with Dynamic SQL will work.

SQL Server 2005 Pivot on Unknown Number of Columns

SQL Server

问SQL Server 2005透视表的列数未知
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server 2005透视表的列数未知EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server 2005透视表的列数未知
EN