blocks|key|1564236|text|那么，对于您的示例和具有有限数量的唯一列的任何示例，这应该可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1564237|select+
++++distinct+a,
++++(select+distinct+t2.b++from+t+t2++where+t1.a=t2.a+and+t2.b='VIEW'),
++++(select+distinct+t2.b+from+t+t2++where+t1.a=t2.a+and+t2.b='EDIT')
from+t+t1|code-block|syntax|javascript|1564238|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Well, for your sample and any with a limited number of unique columns, this should do it.

<pre><code>select 
 distinct a,
 (select distinct t2.b from t t2 where t1.a=t2.a and t2.b='VIEW'),
 (select distinct t2.b from t t2 where t1.a=t2.a and t2.b='EDIT')
from t t1
</code></pre>

blocks|key|1779370|text|请记住，MAX聚合函数将对文本和数字起作用。这个查询只需要扫描一次表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779371|SELECT+Action,
+++++++MAX(+CASE+data+WHEN+'View'+THEN+data+ELSE+''+END+)+ViewCol,+
+++++++MAX(+CASE+data+WHEN+'Edit'+THEN+data+ELSE+''+END+)+EditCol
+FROM+t
+GROUP+BY+Action|code-block|syntax|javascript|1779372|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.

<pre><code>SELECT Action,
 MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol, 
 MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
 FROM t
 GROUP BY Action
</code></pre>

blocks|key|1779409|text|如果您特别想要使用SQL+Server+PIVOT函数，那么假设您的两个原始列分别称为act和cmd，则此函数应该可以工作。(虽然看起来不是那么漂亮。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779410|SELECT+act+AS+'Action',+[View]+as+'View',+[Edit]+as+'Edit'
FROM+(
++++SELECT+act,+cmd+FROM+data
)+AS+src
PIVOT+(
++++MAX(cmd)+FOR+cmd+IN+([View],+[Edit])
)+AS+pvt|code-block|syntax|javascript|1779411|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you specifically want to use the SQL Server PIVOT function, then this should work, assuming your two original columns are called act and cmd. (Not that pretty to look at though.)

<pre><code>SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'
FROM (
 SELECT act, cmd FROM data
) AS src
PIVOT (
 MAX(cmd) FOR cmd IN ([View], [Edit])
) AS pvt
</code></pre>

blocks|key|1779426|text|来自http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1779427|SELECT+CUST,+PRODUCT,+QTY
FROM+Product)+up
PIVOT
(+SUM(QTY)+FOR+PRODUCT+IN+(VEG,+SODA,+MILK,+BEER,+CHIPS))+AS+pvt)+p
UNPIVOT
(QTY+FOR+PRODUCT+IN+(VEG,+SODA,+MILK,+BEER,+CHIPS)
)+AS+Unpvt
GO|code-block|syntax|javascript|1779428|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/^0|2|2C|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

From <a href="http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/" rel="noreferrer">http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/</a>:

<pre><code>SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
</code></pre>

blocks|key|1779451|text|With+pivot_data+as
(
select+
action,+--+grouping+column
view_edit+--+spreading+column
from+tbl
)
select+action,+[view],+[edit]
from+++pivot_data
pivot++(+max(view_edit)+for+view_edit+in+([view],+[edit])+)+as+p;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1779452|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>With pivot_data as
(
select 
action, -- grouping column
view_edit -- spreading column
from tbl
)
select action, [view], [edit]
from pivot_data
pivot ( max(view_edit) for view_edit in ([view], [edit]) ) as p;
</code></pre>

blocks|key|1564437|text|我遇到过这样一种情况:我正在解析字符串，而字符串的前两个位置是一个医疗索赔编码标准的字段名。所以我会去掉字符串，得到F4、UR和UQ之类的值。对于一个用户来说，这在一条记录或几条记录上是很棒的。但是，当我想要查看数百条记录和所有用户的值时，它需要是一个轴心点。这是非常棒的，特别是对于导出大量记录到excel。我收到的具体报告请求是“每次有人提交对苯那曲利的索赔时，他们在字段F4、UR和UQ中提交了什么值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1564438|PIVOT(
++min(value)
++FOR+ColTitle+in([F4],+[UR],+[UQ])
+)|code-block|syntax|javascript|1564439|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had a situation where I was parsing strings and the first two positions of the string in question would be the field names of a healthcare claims coding standard. So I would strip out the strings and get values for F4, UR and UQ or whatnot. This was great on one record or a few records for one user. But when I wanted to see hundreds of records and the values for all usersz it needed to be a PIVOT. This was wonderful especially for exporting lots of records to excel. The specific reporting request I had received was "every time someone submitted a claim for Benadryl, what value did they submit in fields F4, UR, and UQ. I had an OUTER APPLY that created the ColTitle and the value fields below

<pre><code>PIVOT(
 min(value)
 FOR ColTitle in([F4], [UR], [UQ])
 )
</code></pre>

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following.

<pre><code>Action1 VIEW 
Action1 EDIT 
Action2 VIEW 
Action3 VIEW 
Action3 EDIT 
</code></pre>

I would like to use PIVOT (if even possible) to make the results like so:

<pre><code>Action1 VIEW EDIT 
Action2 VIEW NULL 
Action3 VIEW EDIT 
</code></pre>

Is this even possible with the PIVOT functionality?

SQL Server: Examples of PIVOTing String data

SQL Server

试图找到一些简单的SQL Server PIVOT示例。我发现的大多数例子都涉及到对数字进行计数或求和。我只想旋转一些字符串数据。例如，我有一个查询返回以下内容。Action1 VIEW  Action1 EDIT  Action2 VIEW  Action3 VIEW  Action3 EDIT  我想使用PIVOT...

问SQL Server: PIVOTing字符串数据示例
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server: PIVOTing字符串数据示例EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server: PIVOTing字符串数据示例
EN