如何在“in"中编写选择查询数据透视表的子句? [英] How to write select query in "in" clause of pivot table?

问题描述

我是 Sql 编程的新手.

Hi I am new to Sql Programming.

我有一个表Temp"，它有两个字段Name"&软件"

I have a table "Temp" having two fields "Name" & "Software"

我正在旋转一个表格，向他显示软件的名称和版本号.

I am pivoting a table that displays the name and number of versions of software to him.

我对数据透视表的查询是:

My query for pivot is :

select * from temp
pivot(count(Software) for Software in ([Professional],[Personal],[Standard])) as PVT

但不是在中给出静态值([Professional],[Personal],[Standard])

我想从选择查询中选择软件名称应该是:select distinct software from temp

I want to select the software names from select query that should be : select distinct software from temp

当我将这个选择查询写入 in 子句时，它会出错.

When I write this select query to in clause it gives error.

我如何实现这一目标?

请帮忙.提前致谢.

推荐答案

Declare @cols nvarchar(max)
select @cols = 
stuff( ( select distinct  ',[' + Ltrim(rtrim(Software)) +']' from temp FOR XML PATH('')),1,1,'');

EXEC('select * from temp pivot(count(Software) for Software in ('+@cols+')) as PVT')  

@cols 变量将包含从查询中提取的行 select distinct Software from temp 作为 XML 格式:即 [Standard],[Personal],[Professional] 然后使用 EXEC() 函数将结果发送到数据透视查询语句.

The @cols variable will contain the rows fetched from the query select distinct Software from temp as XML format: that is [Standard],[Personal],[Professional] and then the result is sent to the pivot query statement using EXEC() function.

这篇关于如何在“in"中编写选择查询数据透视表的子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！