Join multiple row of records into single table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

below are my SQL codes:

SQL

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

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.

Posted 30-Jun-14 18:19pm

Jamie888

Add a Solution

Comments

Jamie888 1-Jul-14 0:22am

the output after beging transfer into a table should be the same as the output above.

syed shanu 1-Jul-14 0:25am

Your question is not clear ,Improve your question and what exactly you need what is 2 rows int a table ,rows will be inside table only "i want to make the 2 rows into ONE table not ROW." what this mean

3 solutions

Solution 1

Try SELECT…INTO[^]

Posted 30-Jun-14 18:39pm

Peter Leow

Solution 2

TRY LIKE THIS

SQL

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)

Posted 30-Jun-14 18:46pm

syed shanu

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2014-06-30T19:12:00

Add this line at the beginning of your code

SQL

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

At the end modify the SQL like below

SQL

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