将多行记录连接到单个表中 [英] Join multiple row of records into single table

问题描述

我有以下SQL代码，它们会返回以下输出：

Hi, i have the following SQL codes which will return me the following output:

TargetDate ActualDate Percentage Month
======================================
    2          1          80       8
    1          1         100       9

以下是我的SQL代码：

below are my SQL codes:

CREATE TABLE #TargetDateTblx(IssueID VARCHAR(100), 
              TargetDate VARCHAR(100))
INSERT [#TargetDateTblx]
SELECT issueid, 
       [fielddata] AS TargetDate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 215
 
CREATE TABLE #ActualDateTblx(IssueID VARCHAR(100), 
              ActualDate VARCHAR(100))
INSERT [#ActualDateTblx]
SELECT issueid, 
       [fielddata] AS ActualDate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 217
 
CREATE TABLE #CompleteRateTblx(IssueID VARCHAR(100), 
              CompleteRate VARCHAR(100))
INSERT [#CompleteRateTblx]
SELECT issueid, 
       [fielddata] AS CompleteRate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 234
 
CREATE TABLE #ALLTB(IssueID VARCHAR(100), 
              UserID VARCHAR(100), 
              UserName VARCHAR(100), 
              TargetDate VARCHAR(100), 
              ActualDate VARCHAR(100), 
              CompleteRate VARCHAR(100), 
              Months VARCHAR(100))
INSERT [#ALLTB]
SELECT a.issueid, 
       a.userid,
       b.firstname,
       c.TargetDate,
       d.ActualDate,
       e.CompleteRate,
       SUBSTRING(c.TargetDate, 5, 1) AS Month
FROM dbo.gemini_issueresources A, 
     dbo.gemini_users B, 
     #TargetDateTblx c, 
     #ActualDateTblx d, 
     #CompleteRateTblx e
WHERE a.userid = b.userid 
  AND a.issueid = c.IssueID 
  AND  a.issueid = d.IssueID 
  AND  a.issueid = e.IssueID
 
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, 
       SUM(CONVERT(INT, ActualDate)) AS ActualDate, 
       SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) 
    AS CompletePercentage, Months
FROM #FORMULA
GROUP BY Months

如何转移2中的输出行成表。 FIY，输出是正确的，我想将2行放入ONE表而不是ROW。任何帮助将不胜感激。

how can i transfer the output which is in 2 rows into a table. FIY, the output is correct and i want to make the 2 rows into ONE table not ROW. Any help would be appreciated.

推荐答案

尝试 SELECT ... INTO [ ^ ]

尝试喜欢这个

TRY LIKE THIS

CREATE TABLE #ALLTB(
              TargetDate VARCHAR(100), 
              ActualDate VARCHAR(100), 
              CompleteRate VARCHAR(100), 
    INSERT [#ALLTB]
SELECT
	CASE WHEN C.customfieldid = 215 THEN  C.fielddata END AS TargetDate,
	CASE WHEN C.customfieldid = 217 THEN C.fielddata END AS ActualDate,
	CASE WHEN C.customfieldid = 234 THEN  C.fielddata END AS CompleteRate,
 FROM
	dbo.gemini_issueresources A 
	INNER JOIN dbo.gemini_users B ON  a.userid = b.userid
	INNER JOIN rndbug.dbo.gemini_customfielddata ON a.issueid = c.IssueID
	
	SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, 
       SUM(CONVERT(INT, ActualDate)) AS ActualDate, 
       SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) 
    AS CompletePercentage,
    SUBSTRING(c.TargetDate, 5, 1) AS Months
FROM #ALLTB
GROUP BY SUBSTRING(c.TargetDate, 5, 1)

在代码开头添加此行

Add this line at the beginning of your code

CREATE TABLE #OutPutTBL(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE DECIMAL(10,2),
              MONTH INT(100))

最后修改SQL如下

At the end modify the SQL like below

SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate,
       SUM(CONVERT(INT, ActualDate)) AS ActualDate,
       SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate))
    AS CompletePercentage, Months
      INTO #OutputTBL
FROM #FORMULA
GROUP BY Months

这篇关于将多行记录连接到单个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！