从不同的表中检索列名？

I have a "datadump" table that has a bunch of mixed performance-related data. Something like:

我有一个“datadump”表,其中包含一堆混合性能相关的数据。就像是:

MachID  TestDate  MachType  Value1   Value2    ... 
00001   01/01/09  Server    15        48       
00001   01/02/09  Server    16        99       
19999   01/01/09  Switch    32        4.9880   
19999   01/02/09  Switch    32        5.8109

The trick is that the "values" columns MEAN different things for different types of machines. So we have a "xRef" table that looks like:

诀窍在于,对于不同类型的机器,“值”列意味着不同的东西。所以我们有一个“xRef”表,看起来像:

MachType       Column   Description
Server         Value1   Users Connected
Server         Value2   % CPU _total
Switch         Value1   Number of Ports
Switch         Value2   packets/ms 
...

I know, weird structure, but I didn't make it, and can't change it.

我知道,奇怪的结构,但我没有成功,也无法改变它。

I'd like to somehow "inner join" these so I can query the appropriate column headers based on the type of data. Something like this for the servers:

我想以某种方式“内连接”这些,以便我可以根据数据类型查询相应的列标题。这样的服务器:

MachID  TestDate  MachType  Users Connected     % CPU _total    Total RAM
00001   01/01/09  Server    15                  48              4096
00001   01/02/09  Server    16                  99              4096

and this for the switches:

这对于开关:

MachID  TestDate  MachType  Number of Ports   packets/ms  Total Cumulative kb
19999   01/01/09  Switch    32                4.9880      1024547
19999   01/02/09  Switch    32                5.8109      1029450

Is there a way to do this without doing individual hard-coded queries for each type?

有没有办法在不对每种类型进行单独的硬编码查询的情况下执行此操作?

Note: I will only need to query one type of object at a time. Most likely, I'll only be looking at all results between particular dates for a single MachID, if that helps. This is MS SQL 2000.

注意:我只需要一次查询一种类型的对象。最有可能的是,如果有帮助的话,我只会查看单个MachID的特定日期之间的所有结果。这是MS SQL 2000。

Thanks!

4 个解决方案

#1

This will do them all together - you can modify as appropriate if you want them all split up.

这将一起完成 - 如果您希望它们全部拆分,您可以根据需要进行修改。

DECLARE @template AS varchar(max)
DECLARE @sql AS varchar(max)
DECLARE @column_list AS varchar(max)
SELECT  @column_list = COALESCE(@column_list + ', ', '')
        + QUOTENAME([Description])
FROM    xRef

SET @template = ';
WITH    up
          AS (
              SELECT    MachID
                       ,TestDate
                       ,MachType
                       ,[COLUMN]
                       ,[Value]
              FROM      datadump UNPIVOT ( [Value] FOR [Column] IN ([Value1], [Value2]) ) AS unpvt
             )
             ,ready AS (
    SELECT  machID
           ,TestDate
           ,up.MachType
           ,[Description]
           ,up.[Value]
    FROM    up
    INNER JOIN xRef
            ON xRef.machType = up.MachType
               AND xRef.[Column] = up.[Column]
)
SELECT * FROM ready
PIVOT (SUM([Value]) FOR [Description] IN ({@column_list})) AS pvt
'

machID TestDate                MachType Users Connected                         % CPU _total                            Number of Ports                         packets/ms
------ ----------------------- -------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
00001  2009-01-01 00:00:00.000 Server   15.000000000000000                      48.000000000000000                      NULL                                    NULL
00001  2009-01-02 00:00:00.000 Server   16.000000000000000                      99.000000000000000                      NULL                                    NULL
19999  2009-01-01 00:00:00.000 Switch   NULL                                    NULL                                    32.000000000000000                      4.988000000000000
19999  2009-01-02 00:00:00.000 Switch   NULL                                    NULL                                    32.000000000000000                      5.810900000000000

#2

A dynamic sql option would be (written out as a query rather than made into a proc):

一个动态的sql选项将(写成一个查询而不是一个proc):

declare @machtype varchar(40) --stored proc parameter?
set @machtype = 'Switch' --or 'Server'

declare @sql nvarchar(4000)

set @sql = 'select
    MachID,
    TestDate,
    MachType,
    Value1 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value1') + ''',
    Value2 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value2') + ''',
    Value3 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value3') + '''
from 
    dbo.datadump
where
    machtype = ''' + @machtype + ''''

exec sp_executesql @sql

If you find that simply too ugly for you then wrapping the logic for getting the column name in a function would tidy it up:

如果你发现这对你来说太难看了,那么在函数中包装获取列名的逻辑会整理它:

create function dbo.ColNameForDataDump(
    @machtype varchar(40),
    @column varchar(40)
)
RETURNS varchar(40)
as
begin

    declare @col_desc varchar(40)
    select
        @col_desc = [description]
    from
        dbo.xref
    where
        machtype = @machtype
        and [column] = @column

    return @col_desc
end

Then your dynamic SQL will look more like:

然后你的动态SQL看起来更像:

declare @machtype varchar(40) --stored proc parameter?
set @machtype = 'Switch' --or 'Server'

declare @sql nvarchar(4000)

set @sql = 'select
    MachID,
    TestDate,
    MachType,
    Value1 as ''' + dbo.ColNameForDataDump(@machtype, 'Value1') + ''',
    Value2 as ''' + dbo.ColNameForDataDump(@machtype, 'Value2') + ''',
    Value3 as ''' + dbo.ColNameForDataDump(@machtype, 'Value3') + '''
from 
    dbo.datadump
where
    machtype = ''' + @machtype + ''''

exec sp_executesql @sql

Finally a passing point / comment on the code above: you mentioned that you are on SQL Server 2000 so make sure when you do have to write some dynamic sql to define it as an nvarchar and use sp_executesql to call it...thereby negating some of the performance pain of having to go dynamic.

最后是上面代码的传递点/注释:你提到你在SQL Server 2000上,所以确保你必须编写一些动态sql来定义它为nvarchar并使用sp_executesql来调用它...从而否定一些不得不动态的表现痛苦。

#3

Create a table storing the header-name for each value for that individual type of query.

创建一个表,用于存储该单个查询类型的每个值的header-name。

Then, create a stored procedure and use Dynamic SQL to fill in the column name as drawn from that table.

然后,创建一个存储过程并使用动态SQL填充从该表中绘制的列名。

#4

Since you can't change the data model I would suggest putting the presentation code into the presentation layer of your application. Have a table that gives you the column headings to use based on the results being requested and go from there.

由于您无法更改数据模型,因此建议将演示文稿代码放入应用程序的表示层。有一个表格,根据所请求的结果为您提供列标题,并从那里开始。

#1