如何编写 SQL 查询以水平显示值 [英] How to write a SQL query to present values horizontally
问题描述
假设我有一张名为 Jobs
的表:
Suppose I have one table called Jobs
:
CREATE TABLE [Jobs]
(
[JOBID] [int] IDENTITY(1,1) NOT NULL,
[PARTDESC] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderPlacedBy] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SpecialistName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priority] [int] NOT NULL,
[Symptoms] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShopNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JobType] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JobState] [nvarchar](32) COLLATE Latin1_General_CI_AS NULL,
[JobAddedDate] [datetime] NOT NULL,
[JobStartedDate] [datetime] NULL,
[JobFinishedDate] [datetime] NULL,
[JobShippedDate] [datetime] NULL,
[RecievedDate] [datetime] NULL
)
我想横向查看专家姓名和他的工作 ID.
I want to see the specialist name and his jobs IDs horizontally.
ANA 201,502,605,701,774
BEN 102,103,051
JEN 705,401,402,509,409,408
专家可能有 n
个工作.假设专家 ANA
有 10 个工作,而 BEN 有 5 个工作.
A specialist may have n
jobs. Suppose specialist ANA
has 10 jobs where BEN has 5 jobs.
通过这种方式,我想横向向专家展示他的工作,其中每个专家的工作数量可能会有所不同.
In this way I want to show specialist his jobs horizontally where the number of jobs may vary per specialist.
如何在 SQL 中执行此操作?
How can I do this in SQL?
推荐答案
Marc 是对的.不要在 SQL 中执行此操作,而应在表示级别执行此操作.不过,如果你想这样做,看看这里:如何从 sql 中具有动态内容的 2 个不同表中返回 1 个单行数据
Marc is right. Don't do this in SQL, do it at the presentation level. Still, if you want to do this, take a look here: How to return 1 single row data from 2 different tables with dynamic contents in sql
适用于您的情况,代码可能是:
Applied to your situation, the code might be:
select SpecialistName , LEFT(JobsIds, len(JobsIds)-1) as JobsIds from
(SELECT j.SpecialistName ,
( SELECT cast(j1.JobsId as varchar(10)) + ','
FROM Jobs j1
WHERE j1.SpecialistName = j.SpecialistName
ORDER BY JobId
FOR XML PATH('') ) AS JobsIds
FROM Jobs j
GROUP BY SpecialistName )A;
这篇关于如何编写 SQL 查询以水平显示值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!