如何在SQL中生成以下输出 [英] How to generate following output in SQL

问题描述

我的表格给出如下记录
选择*来自EMP 
 EMPID VehicalID 
 1 AB-1 
 1 CD-1 
 1 DE-1 
 2 XY-1 
 2 PQ-2 
 --- ====================== ================================================== ======== --- 
我想要这样的输出
 EPID Veh1 Veh2 veh3 
 1 AB-1 CD-1 DE-1 
 2 XY-1 PQ-2 

解决方案

使用 pivot（s） [ ^ ]，因为聚合函数 [ ^ ]只能用于数字数据。





试试这个：

  DECLARE   @ emp   TABLE （EMPID  INT ，VehicalID  NVARCHAR （ 30 ））
 
  INSERT   INTO   @ emp （EMPID，VehicalID）
  VALUES （ 1 ，'  AB-1'）
  INSERT   INTO   @ emp （EMPID，VehicalID）
  VALUES （ 1 ，'  CD-1'） 
  INSERT   INTO   @ emp  （EMPID，VehicalID）
  VALUES （ 1 ，'  DE-1'）
  INSERT   INTO   @ emp （EMPID，VehicalID）
  VALUES （  2 ，'  XY-1'） 
  INSERT   INTO   @ emp  （EMPID，VehicalID）
  VALUES （ 2 ，'  PQ-2'）
 
  DECLARE   @ tmp  < span class =code-keyword> TABLE （CID  INT   IDENTITY （ 1 ， 1 ），EMPID  INT ，VehNo  INT ，VehicalID  NVARCHAR （ 30 ））
 
  INSERT   INTO   @ tmp （EMPID，VehNo，VehicalID）
  SELECT  EMPID，ROW_NUMBER（） OVER （< span class =code-keyword> PARTITION   BY  EMPID  ORDER   BY  VehicalID） AS  VehNo，VehicalID 
  FROM  < span class =code-sdkkeyword> @ emp  
 
  SELECT   DISTINCT  total.EMPID，t1.VehicalID  AS  Veh1，t2.VehicalID  AS  Veh2，t3.VehicalID  AS  Veh3 
  FROM  < span class =code-sdkkeyword> @ tmp   AS 总计
  LEFT   JOIN   @ tmp   AS  t1  ON  total.EMPID = t1.EMPID  AND  t1.VehNo =  1  
  LEFT   JOIN   @ tmp   AS  t2  ON  total.EMPID = t2.EMPID  AND  t2.VehNo =  2  
  LEFT   JOIN   @ tmp   AS  t3  ON  total.EMPID = t3.EMPID  AND  t3.VehNo =  3  

退货：

< pre lang =text> EMPID Veh1 Veh2 Veh3
1 AB-1 CD-1 DE-1
2 PQ-2 XY-1 NULL

I have table which gives records as below
Select * From EMP
EMPID  VehicalID
1	AB-1
1	CD-1
1	DE-1
2	XY-1
2       PQ-2
---================================================================================---
I want output like this 
EPID              Veh1             Veh2             veh3
1                 AB-1             CD-1             DE-1
2                 XY-1             PQ-2

解决方案

[EDIT #1]
There is not possible to achieve that using pivot(s)[^], because aggregate function(s)[^] can be used only on numeric data.
[EDIT]

Try this:

DECLARE @emp TABLE(EMPID INT, VehicalID NVARCHAR(30))

INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'AB-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'CD-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'DE-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'XY-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'PQ-2')

DECLARE @tmp TABLE (CID INT IDENTITY(1,1), EMPID INT, VehNo INT, VehicalID NVARCHAR(30))

INSERT INTO @tmp (EMPID, VehNo, VehicalID)
SELECT EMPID, ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY VehicalID) AS VehNo, VehicalID
FROM @emp

SELECT DISTINCT total.EMPID, t1.VehicalID AS Veh1, t2.VehicalID AS Veh2, t3.VehicalID AS Veh3
FROM @tmp AS total
    LEFT JOIN @tmp AS t1 ON total.EMPID = t1.EMPID AND t1.VehNo = 1
    LEFT JOIN @tmp AS t2 ON total.EMPID = t2.EMPID AND t2.VehNo = 2
    LEFT JOIN @tmp AS t3 ON total.EMPID = t3.EMPID AND t3.VehNo = 3

Returns:

EMPID   Veh1    Veh2    Veh3
1	AB-1	CD-1	DE-1
2	PQ-2	XY-1	NULL

这篇关于如何在SQL中生成以下输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！