blocks|key|2506463|text|您可以手动旋转：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2506464|select
++++SubId,
++++max(case+when+ProcessNumber+=+802+then+ReadTime+end)+as+[802Time],
++++max(case+when+ProcessNumber+=+802+then+Lineage+end)+as+[802Lineage],
++++....
from+SourceTable
group+by+SubId|code-block|syntax|javascript|2506465|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|$]]

you can pivot manually:

<pre><code>select
 SubId,
 max(case when ProcessNumber = 802 then ReadTime end) as [802Time],
 max(case when ProcessNumber = 802 then Lineage end) as [802Lineage],
 ....
from SourceTable
group by SubId
</code></pre>

blocks|key|1429430|text|按照注释中的要求，连接两个枢轴表的示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1429431|CREATE+TABLE+#MyTable+(SubId+int,+ProcessNumber+int,+Lineage+varchar(16),+ReadTime+datetime)
INSERT+INTO+#MyTable 
++++++++(SubID,+ProcessNumber,+Lineage,+ReadTime)
VALUES
++++++++(1,+9,+'A',+GETDATE()),
++++++++(1,+8,+'A',+GETDATE()),
++++++++(1,+7,+'B',+GETDATE()),
++++++++(2,+9,+'C',+GETDATE()),
++++++++(2,+8,+'C',+GETDATE())
SELECT+* 
FROM+(
+++Select+SubId,+[9]+as+[9Time],+[8]+as+[8Time],+[7]+as+[7Time]
+++From
+++(Select+SubId,+ProcessNumber,+ReadTime+From+#MyTable)+as+SourceTable
+++PIVOT(Max(ReadTime)+For+ProcessNumber+IN+([9],[8],[7],[6]))+as+PivotTable1
)+AS+T1
INNER+JOIN+(
+++Select+SubId,+[9]+as+[9Lineage],+[8]+as+[8Lineage],+[7]+as+[7Lineage]
+++From
+++(Select+SubId,+ProcessNumber,+Lineage+From+#MyTable)+as+SourceTable
+++PIVOT(Max(Lineage)+For+ProcessNumber+IN+([9],[8],[7],[6]))+as+PivotTable1
+)+AS+T2
ON+T1.SubId+=+T2.SubId
GO|code-block|syntax|javascript|1429432|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|$]]

Example of joining two pivot tables, as requested in comments.

<pre><code>CREATE TABLE #MyTable (SubId int, ProcessNumber int, Lineage varchar(16), ReadTime datetime)
INSERT INTO #MyTable 
 (SubID, ProcessNumber, Lineage, ReadTime)
VALUES
 (1, 9, 'A', GETDATE()),
 (1, 8, 'A', GETDATE()),
 (1, 7, 'B', GETDATE()),
 (2, 9, 'C', GETDATE()),
 (2, 8, 'C', GETDATE())
SELECT * 
FROM (
 Select SubId, [9] as [9Time], [8] as [8Time], [7] as [7Time]
 From
 (Select SubId, ProcessNumber, ReadTime From #MyTable) as SourceTable
 PIVOT(Max(ReadTime) For ProcessNumber IN ([9],[8],[7],[6])) as PivotTable1
) AS T1
INNER JOIN (
 Select SubId, [9] as [9Lineage], [8] as [8Lineage], [7] as [7Lineage]
 From
 (Select SubId, ProcessNumber, Lineage From #MyTable) as SourceTable
 PIVOT(Max(Lineage) For ProcessNumber IN ([9],[8],[7],[6])) as PivotTable1
 ) AS T2
ON T1.SubId = T2.SubId
GO
</code></pre>

blocks|key|1429456|text|您可以使用PIVOT函数来获得结果，但是您必须将Lineage和ReadTime列从多个列卸载到多个行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1429457|由于您使用的是Server+2012，所以可以使用交叉应用和值来解除数据的枢轴：|1429458|select+subid,+
++colname+=+cast(processNumber+as+varchar(10))+%2B+colname,
++value
from+mytable
cross+apply
(
++values
++++('Lineage',+Lineage),
++++('ReadTime',+convert(varchar(20),+readtime,+120))
)+c+(colname,+value)|code-block|syntax|javascript|1429459|见与Demo。这将将当前数据转换为下列格式：|1429460|%7C+++++++SUBID+%7C++++++COLNAME+%7C+++++++++++++++VALUE+%7C
%7C-------------%7C--------------%7C---------------------%7C
%7C+12010231146+%7C+++802Lineage+%7C++++++++++++++PBG12A+%7C
%7C+12010231146+%7C++802ReadTime+%7C+2012-01-02+21:44:00+%7C
%7C+12010231146+%7C++1000Lineage+%7C++++++++++++++PBG12A+%7C
%7C+12010231146+%7C+1000ReadTime+%7C+2012-01-02+21:43:00+%7C
%7C+12010231146+%7C++1190Lineage+%7C++++++++++++++PBG11B+%7C
%7C+12010231146+%7C+1190ReadTime+%7C+2012-01-03+14:36:00+%7C|1429461|一旦数据采用这种格式，那么您就可以很容易地应用PIVOT函数来获得您的最终结果：|1429462|select+*
from
(
++select+subid,+
++++colname+=+cast(processNumber+as+varchar(10))+%2B+colname,
++++value
++from+mytable
++cross+apply
++(
++++values
++++++('Lineage',+Lineage),
++++++('ReadTime',+convert(varchar(20),+readtime,+120))
++)+c+(colname,+value)
)+d
pivot
(
++max(value)
++for+colname+in+([802Lineage],+[802ReadTime],
++++++++++++++++++[1000Lineage],+[1000ReadTime],
++++++++++++++++++[1190Lineage],+[1190ReadTime])
)+piv;|1429463|见与Demo。|1429464|如果您想要转换的行数目有限，那么上面的操作非常好，但是如果您有一个未知的编号，那么您可以使用动态SQL：|1429465|DECLARE+@cols+AS+NVARCHAR(MAX),
++++@query++AS+NVARCHAR(MAX)

select+@cols+=+STUFF((SELECT+','+%2B+QUOTENAME(cast(processnumber+as+varchar(10))%2Bcol)+
++++++++++++++++++++from+mytable
++++++++++++++++++++cross+apply
++++++++++++++++++++(
++++++++++++++++++++++select+'Lineage',+0+union+all
++++++++++++++++++++++select+'ReadTime',+1
++++++++++++++++++++)+c+(col,+so)
++++++++++++++++++++group+by+processnumber,+col,+so
++++++++++++++++++++order+by+processnumber,+so
++++++++++++FOR+XML+PATH(''),+TYPE
++++++++++++).value('.',+'NVARCHAR(MAX)')+
++++++++,1,1,'')


set+@query+=+'SELECT+subid,+'+%2B+@cols+%2B+'+
++++++++++++from+
++++++++++++(
++++++++++++++select+subid,+
++++++++++++++++colname+=+cast(processNumber+as+varchar(10))+%2B+colname,
++++++++++++++++value
++++++++++++++from+mytable
++++++++++++++cross+apply
++++++++++++++(
++++++++++++++++values
++++++++++++++++++(''Lineage'',+Lineage),
++++++++++++++++++(''ReadTime'',+convert(varchar(20),+readtime,+120))
++++++++++++++)+c+(colname,+value)
++++++++++++)+x
++++++++++++pivot+
++++++++++++(
++++++++++++++++max(value)
++++++++++++++++for+colname+in+('+%2B+@cols+%2B+')
++++++++++++)+p+'

execute+sp_executesql+@query;|1429466|见与Demo。这样做的结果是：|1429467|%7C+++++++SUBID+%7C+802LINEAGE+%7C+++++++++802READTIME+%7C+1000LINEAGE+%7C++++++++1000READTIME+%7C+1190LINEAGE+%7C++++++++1190READTIME+%7C+1605LINEAGE+%7C++++++++1605READTIME+%7C+1745LINEAGE+%7C++++++++1745READTIME+%7C+1790LINEAGE+%7C++++++++1790READTIME+%7C+1990LINEAGE+%7C++++++++1990READTIME+%7C+2690LINEAGE+%7C++++++++2690READTIME+%7C+2795LINEAGE+%7C++++++++2795READTIME+%7C+2990LINEAGE+%7C++++++++2990READTIME+%7C+3090LINEAGE+%7C++++++++3090READTIME+%7C+3290LINEAGE+%7C++++++++3290READTIME+%7C
%7C-------------%7C------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C-------------%7C---------------------%7C
%7C+12010231146+%7C+++++PBG12A+%7C+2012-01-02+21:44:00+%7C++++++PBG12A+%7C+2012-01-02+21:43:00+%7C++++++PBG11B+%7C+2012-01-03+14:36:00+%7C++++++PBG11B+%7C+2012-01-03+15:15:00+%7C++++++PBG11A+%7C+2012-01-03+15:16:00+%7C++++++PBG11A+%7C+2012-01-03+15:19:00+%7C++++++PBG11A+%7C+2012-01-03+15:23:00+%7C++++++PBG11A+%7C+2012-01-03+15:32:00+%7C++++++PBG11A+%7C+2012-01-03+15:39:00+%7C++++++PBG11A+%7C+2012-01-03+15:41:00+%7C++++++PBG11A+%7C+2012-01-03+15:46:00+%7C++++++PBG11A+%7C+2012-01-03+15:47:00+%7C|1429468|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/595af/8|1|http://sqlfiddle.com/#!3/595af/2|2|http://sqlfiddle.com/#!3/595af/7^0|O|7|W|8|0|0|0|1|5|0|0|0|0|0|1|5|1|0|0|0|1|5|2|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1L|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1M|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1N|8|@]|D|@$9|1O|A|1P|1|1Q]]|E|$]]|$1|O|3|P|5|J|7|1R|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1S|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|1T|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|1U|8|@]|D|@$9|1V|A|1W|1|1X]]|E|$]]|$1|W|3|X|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|J|7|1Z|8|@]|D|@]|E|$K|L]]|$1|10|3|11|5|6|7|20|8|@]|D|@$9|21|A|22|1|23]]|E|$]]|$1|12|3|13|5|J|7|24|8|@]|D|@]|E|$K|L]]|$1|14|3|-4|5|6|7|25|8|@]|D|@]|E|$]]]|15|$16|$5|17|18|19|E|$1A|1B]]|1C|$5|17|18|19|E|$1A|1D]]|1E|$5|17|18|19|E|$1A|1F]]]]

You can use the PIVOT function to get the result but you will have to unpivot the <code>Lineage</code> and <code>ReadTime</code> columns from the multiple columns into multiple rows. 

Since you are using SQL Server 2012 you can unpivot the data using CROSS APPLY with VALUES:

<pre><code>select subid, 
 colname = cast(processNumber as varchar(10)) + colname,
 value
from mytable
cross apply
(
 values
 ('Lineage', Lineage),
 ('ReadTime', convert(varchar(20), readtime, 120))
) c (colname, value)
</code></pre>

See <a href="http://sqlfiddle.com/#!3/595af/8" rel="nofollow">SQL Fiddle with Demo</a>. This will convert your current data into the format:

<pre><code>| SUBID | COLNAME | VALUE |
|-------------|--------------|---------------------|
| 12010231146 | 802Lineage | PBG12A |
| 12010231146 | 802ReadTime | 2012-01-02 21:44:00 |
| 12010231146 | 1000Lineage | PBG12A |
| 12010231146 | 1000ReadTime | 2012-01-02 21:43:00 |
| 12010231146 | 1190Lineage | PBG11B |
| 12010231146 | 1190ReadTime | 2012-01-03 14:36:00 |
</code></pre>

Once the data is in this format, then you can easily apply the PIVOT function to get your final result:

<pre><code>select *
from
(
 select subid, 
 colname = cast(processNumber as varchar(10)) + colname,
 value
 from mytable
 cross apply
 (
 values
 ('Lineage', Lineage),
 ('ReadTime', convert(varchar(20), readtime, 120))
 ) c (colname, value)
) d
pivot
(
 max(value)
 for colname in ([802Lineage], [802ReadTime],
 [1000Lineage], [1000ReadTime],
 [1190Lineage], [1190ReadTime])
) piv;
</code></pre>

See <a href="http://sqlfiddle.com/#!3/595af/2" rel="nofollow">SQL Fiddle with Demo</a>.

The above works great if you have a limited number of rows that you want to convert, but if you have an unknown number then you can use dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(processnumber as varchar(10))+col) 
 from mytable
 cross apply
 (
 select 'Lineage', 0 union all
 select 'ReadTime', 1
 ) c (col, so)
 group by processnumber, col, so
 order by processnumber, so
 FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)') 
 ,1,1,'')


set @query = 'SELECT subid, ' + @cols + ' 
 from 
 (
 select subid, 
 colname = cast(processNumber as varchar(10)) + colname,
 value
 from mytable
 cross apply
 (
 values
 (''Lineage'', Lineage),
 (''ReadTime'', convert(varchar(20), readtime, 120))
 ) c (colname, value)
 ) x
 pivot 
 (
 max(value)
 for colname in (' + @cols + ')
 ) p '

execute sp_executesql @query;
</code></pre>

See <a href="http://sqlfiddle.com/#!3/595af/7" rel="nofollow">SQL Fiddle with Demo</a>. This gives a result:

<pre><code>| SUBID | 802LINEAGE | 802READTIME | 1000LINEAGE | 1000READTIME | 1190LINEAGE | 1190READTIME | 1605LINEAGE | 1605READTIME | 1745LINEAGE | 1745READTIME | 1790LINEAGE | 1790READTIME | 1990LINEAGE | 1990READTIME | 2690LINEAGE | 2690READTIME | 2795LINEAGE | 2795READTIME | 2990LINEAGE | 2990READTIME | 3090LINEAGE | 3090READTIME | 3290LINEAGE | 3290READTIME |
|-------------|------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|-------------|---------------------|
| 12010231146 | PBG12A | 2012-01-02 21:44:00 | PBG12A | 2012-01-02 21:43:00 | PBG11B | 2012-01-03 14:36:00 | PBG11B | 2012-01-03 15:15:00 | PBG11A | 2012-01-03 15:16:00 | PBG11A | 2012-01-03 15:19:00 | PBG11A | 2012-01-03 15:23:00 | PBG11A | 2012-01-03 15:32:00 | PBG11A | 2012-01-03 15:39:00 | PBG11A | 2012-01-03 15:41:00 | PBG11A | 2012-01-03 15:46:00 | PBG11A | 2012-01-03 15:47:00 |
</code></pre>

I have data that looks as follows:

<img src="https://i.stack.imgur.com/Izb2Q.png" alt="enter image description here">

I would like to pivot this data so that there is only a single row for the SubId. The columns would be SubId, 802Lineage, 802ReadTime, 1000Lineage, 1000ReadTime etc.

If it wasn't for the requirement to have the Lineage included, this would be pretty straightforward, as follows:

<pre><code>Select SubId, [800] as [800Time], [1190] as [1190Time], [1605] as [1605Time]
From
(Select SubId, ProcessNumber, ReadTime From MyTable) as SourceTable
PIVOT
(
 Max(ReadTime)
 For ProcessNumber IN ([800],[802],[1190],[1605])
) as PivotTable;
</code></pre>

I'm just not sure how to do this with the Lineage included. This is for SQL Server 2012

Is it possible to Pivot data like this?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有如下数据：​​我想对这些数据进行枢轴处理，以便SubId只有一行。列为SubId、802 802Lineage、802 802ReadTime、1000 802Lineage、1000ReadTime等。如果不是因为需要包含血统，这将是非常简单的，如下所示：Select SubId, [800] as [800Ti...

问有可能像这样对数据进行数据透视吗？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有可能像这样对数据进行数据透视吗？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有可能像这样对数据进行数据透视吗？
EN