I have below mentioned table :
我有下面提到的表格:
drn RecNum Name Value
----------------------------------------------
1 1 ad1_pk 1
2 1 ad1_address1 P.O. Box 5036
3 1 ad1_address2 NULL
4 1 ad1_address3 NULL
5 1 ad1_ctyfk 56
6 1 ad1_postalcode 80155-5036
7 1 ad1_active Y
8 1 ad1_irstat A
9 1 ad1_irdata NULL
10 1 ad1_at1fk 1
1 2 ad1_pk 2
2 2 ad1_address1 1871 S. Broadway
3 2 ad1_address2 NULL
4 2 ad1_address3 NULL
5 2 ad1_ctyfk 1
6 2 ad1_postalcode 80210
7 2 ad1_active Y
8 2 ad1_irstat A
9 2 ad1_irdata NULL
10 2 ad1_at1fk 1
I am creating the pivot using the below mentioned query:
我正在使用下面提到的查询创建pivot:
declare @var nvarchar(max)
declare @sql nvarchar(max)
set @var = stuff((select distinct ',' + name from temp
for xml path('')),1,1,'') -- **this is giving distinct column list but the order of columns get changed..**
set @sql = 'select * from temp
pivot(max(value) for name in (' + @var + ')) as pvt'
exec sp_executesql @sql
Is there a way to keep the order of the columns unchanged? I want the order of columns listed in @var to be same as in the table.
有办法保持列的顺序不变吗?我希望@var中列出的列的顺序与表中相同。
2 个解决方案
#1
5
Add a GROUP BY and an ORDER BY clause (to replace the DISTINCT) where you build your column list as follows:
添加一个GROUP BY和ORDER BY子句(以替换不同的子句)，您可以在其中构建列列表，如下所示:
set @var = stuff((select ',' + min(name) from temp GROUP BY drn ORDER BY drn
for xml path('')),1,1,'')
And don't forget the the necessary aggregation (I've used MIN()). Thanks @Ionic.
不要忘记必要的聚合(我使用了MIN()))。谢谢@Ionic。
#2
0
This is because you're using a DISTINCT in your SELECT query. If you look at the execution plan, you can see DISTINCT SORT operation. This sorts your result based on the DISTINCT columns you specify, in this case it's Name:
这是因为您在选择查询中使用了一个不同的查询。如果查看执行计划，可以看到不同的排序操作。这将根据您指定的不同列对结果进行排序，在本例中，它的名称是:
To retain the order, you can try this:
为了维持秩序，你可以试试:
set @var = stuff((
select ',' + name
from(
select
name,
drn,
rn = row_number() over(partition by name order by drn)
from temp
)t
where rn = 1
order by drn
for xml path('')),
1,1,'')
#1
5
Add a GROUP BY and an ORDER BY clause (to replace the DISTINCT) where you build your column list as follows:
添加一个GROUP BY和ORDER BY子句(以替换不同的子句)，您可以在其中构建列列表，如下所示:
set @var = stuff((select ',' + min(name) from temp GROUP BY drn ORDER BY drn
for xml path('')),1,1,'')
And don't forget the the necessary aggregation (I've used MIN()). Thanks @Ionic.
不要忘记必要的聚合(我使用了MIN()))。谢谢@Ionic。
#2
0
This is because you're using a DISTINCT in your SELECT query. If you look at the execution plan, you can see DISTINCT SORT operation. This sorts your result based on the DISTINCT columns you specify, in this case it's Name:
这是因为您在选择查询中使用了一个不同的查询。如果查看执行计划，可以看到不同的排序操作。这将根据您指定的不同列对结果进行排序，在本例中，它的名称是:
To retain the order, you can try this:
为了维持秩序，你可以试试:
set @var = stuff((
select ',' + name
from(
select
name,
drn,
rn = row_number() over(partition by name order by drn)
from temp
)t
where rn = 1
order by drn
for xml path('')),
1,1,'')