SQL Server 2012，排除列 [英] SQL Server 2012, exclude column

问题描述

我有SQL查询

; with cte as 
(
   SELECT 
       PARSENAME(REPLACE(replace(replace(replace(replace(dbo.IDENTITY_MAP.Name, 'My Company\', ''), '-VLAN2', ''), '.VLAN2\', ''), '.Instr\', '') , '\' , '.'), 1) as "Site",
       Count (CASE
                 WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%' THEN 1
              END) AS 'SEP-11',
       Count (CASE
                 WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%' THEN 1
              END) AS 'SEP-12',
   FROM   
       dbo.sem_computer
   INNER JOIN 
       [dbo].[V_SEM_COMPUTER] ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
   WHERE 
       dbo.IDENTITY_MAP.Name NOT LIKE '%Servers%'
   GROUP BY 
       PARSENAME(REPLACE(replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') , '\' , '.'),1)
)
select *
from cte
join SEPM_site ss on cte.Site = ss.Site

给出输出我正在寻找------几乎ie

That gives output I am looking for ------ almost i.e.

Site  SEP-11  SEP-12 Rackcode  Circuit  Site

我只需要一个列站点。

我尝试使用列重新创建一个临时表，并将其删除，即

I tried recreating a temporary table with the columns, and dropping it, i.e.

; with cte as (SELECT ...)
select * into temptable
from cte
join SEPM_site ss
 on cte.Site = ss.Site
alter table temptable
drop column cte.Site
select * from temptable
drop table temptable

但是我收到错误

'。'附近的错误语法

Incorrect syntax near '.'

如果我没有指定哪个表网站来自，我会收到错误，

And if I don't specify which table Site is from, I get error,

每个表中的列名必须是唯一的。列表'temptable'中的列名称'Site'被指定多次。

Column names in each table must be unique. Column name 'Site' in table 'temptable' is specified more than once.

但是这就是为什么我要删除重复的列！

But that's why I am trying to remove duplicate column!

谢谢！

推荐答案

选择语句：

select cte.Site, cte.[SEP-11], cte.[SEP-12], ss.Rackcode, ss.Circuit
from cte
join SEPM_site ss
 on cte.Site = ss.Site

您还可以选择cte中的所有列，只选择所需的列：

You can also select all columns in cte and just the ones you want in ss:

select cte.*, ss.Rackcode, ss.Circuit
from cte
join SEPM_site ss
 on cte.Site = ss.Site

这篇关于SQL Server 2012，排除列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！