sql server 2008中另一列下的一列数据 [英] One column data under another column in sql server 2008

问题描述

我有两个表，实体作为一个表中的列和实体，另一个表中的员工列。

我需要显示如下数据：



KBS - 5.0 - APP支持服务

Kamesh K.

Shiva

Sunil





这里KBS - 5.0 - APP Suport Services是一个实体名称这是两个表和其他名称在第二个表中。



这里对于每个实体，我需要打印相应的员工。



我在谷歌试过，我无法得到解决方案。



请帮我解答。

Hi,

I have two tables with entity as a column in one table and entity, employee columns in another table.
I need to show the data as shown below:

KBS - 5.0 - APP Suport Services
Kamesh K.
Shiva
Sunil


Here "KBS - 5.0 - APP Suport Services" is an entity name which is in two tables and other names are in the second table.

Here for each entity, I need to print corresponding employees.

I have tried in google, I could not get the solution.

Please help me in getting the answer.

推荐答案

我假设您有两个名为'entities'和'employees'的表。

'entities'表至少有一个包含实体名称的列和'员工'表中至少有两个与enti有关'实体'表的ty列（我用名称来设定）。

您希望查询显示在单个列实体（作为某种标题）和此实体的员工列表中。等等。如果是这样，请尝试以下查询，我认为它符合这些要求（我还添加了一个您可以轻松删除的[type]列）：

I assume that you have two tables named 'entities' and 'employees'.
'entities' table has at least one column with entity name and the 'employees' table has at least two one of them is related to entity column (I presume by name) of 'entities' table.
You want your query to show in a single column entity (as some kind of header) and an employees list of this entity. And so on. If so, try the following query, I suppose it meets these requirements (I also added a [type] column which you could easily remove):

SELECT 
  CASE WHEN e.employee IS NULL THEN e.entity
       ELSE e.employee END AS employee_entity,
  CASE WHEN e.employee IS NULL THEN 'Entity'
       ELSE 'Employee' END AS [type]
  FROM ( SELECT entities.entity AS entity, employees.employee AS employee
            FROM entities
            JOIN employees
              ON employees.entity = entities.entity
           GROUP BY GROUPING SETS((entities.entity), (entities.entity, employees.employee)) ) AS e
  ORDER BY e.entity, e.employee

我不建议以这种方式呈现数据。为什么？最重要的原因是数据不可排序。



将数据呈现在2列中要好得多，例如：

I would not recommend to present data in that way. Why? The most important reason is that the data are not sortable.

It is much better to present data in 2 columns, ex.:

DECLARE @entities TABLE (entID INT IDENTITY(1,1), entity VARCHAR(50))

INSERT INTO @entities (entity)
VALUES('KBS - APP Suport Services'),
    ('EMS - Employee Management Support'),
    ('DOP - Deparment Of Production')

DECLARE @employees TABLE (empID INT IDENTITY(1,1), employee VARCHAR(50))

INSERT INTO @employees (employee)
VALUES ('Amanda'),
    ('Barbara'),
    ('Celine'),
    ('Dorothy'),
    ('Eduardo'),
    ('Frank'),
    ('Guliver'),
    ('Henry'),
    ('Imany')

DECLARE @entDetails TABLE (entID INT, empID INT)

INSERT INTO @entDetails (entID, empID)
VALUES(1,1),
    (1,2),
    (1,3),
    (2,4),
    (2,5),
    (2,6),
    (3,7),
    (3,8),
    (3,9)

SELECT ent.entity, emp.employee
FROM @entDetails AS ed INNER JOIN @entities AS ent ON ed.entID = ent.entID
    INNER JOIN @employees AS emp ON ed.empID = emp.empID

结果：

Result:

entity					employee
KBS - APP Suport Services		Amanda
KBS - APP Suport Services		Barbara
KBS - APP Suport Services		Celine
EMS - Employee Management Support	Dorothy
EMS - Employee Management Support	Eduardo
EMS - Employee Management Support	Frank
DOP - Deparment Of Production		Guliver
DOP - Deparment Of Production		Henry
DOP - Deparment Of Production		Imany

这篇关于sql server 2008中另一列下的一列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！