如何在SQL SERVER中使用PIVOT运算符获取行中的列值? [英] How to get the column values in a row using PIVOT operator in SQL SERVER ?

问题描述

当前数据:

Current Data :

Ticket            Txn_Name                       Txn_Date
6700196058        ORDER CREATED                  20-Jan-2017 01:33:30 PM    
6700196058        IN-TRANSIT TO CUSTOMER         20-Jan-2017 03:49:00 PM
6700196058        ON HOLD                        23-Jan-2017 04:04:09 PM
6700196058        ORDER CLOSED                   25-Jan-2017 03:06:46 PM

以上数据存储在表中.

我们如何获得此输出.

假设一张票有2个txn_name

This above data is stored in table.

How can we get this Output.

suppose a ticket is having 2 txn_name

Ticket         Status1            status2
6700196058     ORDER CREATED      ON HOLD

假设一张票有3个txn_name

suppose a ticket is having 3 txn_name

Ticket         Status1            status2            Status3
6700196058     ORDER CREATED      ON HOLD            ORDER CLOSED

请帮忙..

我尝试过的事情:

如何在SQL SERVER中使用PIVOT运算符获取行中的列值?

Please help..

What I have tried:

How to get the column values in a Row using PIVOT Operator in SQL SERVER ?

推荐答案

CREATE TABLE MaheshTable(ticket varchar(30),
 txn_name varchar(30),
  txn_date datetime);
insert into MaheshTable(ticket,txn_name,txn_date)VALUES (''6700196058'',''ORDER CREATED'',GETDATE());    
insert into MaheshTable(ticket,txn_name,txn_date)VALUES(''6700196058'',''IN-TRANSIT TO CUSTOMER'',GETDATE());
insert into MaheshTable(ticket,txn_name,txn_date)VALUES(''6700196058'',''ON HOLD'',GETDATE());
insert into MaheshTable(ticket,txn_name,txn_date)VALUES(''6700196058'',''ORDER CLOSED'',GETDATE());

insert into MaheshTable(ticket,txn_name,txn_date)VALUES(''6700196059'',''ORDER CLOSED'',GETDATE());

--select * from MaheshTable;

上面我尝试过首先创建一个带有记录的模式.以下是实际的查询.

The above i have tried first creating a schema with records.Below is the actual query.

Create table temp
(
 ticket varchar(100),
 txn_nme varchar(30),
  txn_date datetime,
statuspi varchar(20)
);
Insert into temp
SELECT * ,ROW_NUMBER() OVER(Partition by ticket ORDER BY ticket) AS Row_Number from MaheshTable;

--select * from temp;

DECLARE @cols AS NVARCHAR(MAX);
 DECLARE   @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct '','' +''Status''+ QUOTENAME(c.statuspi) 
            FROM temp c
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
        ,0,0,'''');
--execute(@cols);        
set @query = ''Select ticket,''+@cols+''from(select ticket,statuspi from temp)x pivot(VAR(txn_nme) for txn_name in (''+@cols+''))p'';
--Print @query;
execute(@query);

这篇关于如何在SQL SERVER中使用PIVOT运算符获取行中的列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！