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

本文介绍了如何在SQL SERVER中使用PIVOT运算符获取行中的列值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当前数据:

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);