SQL Table Join issue

I need the query below modified to make this output, instead of the one listed.
TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)
--------------------------------------------------------------------
NAME     START    END     PAUSE
001        20           50         70
002        25           55         75
SELECT tblNAME.NAME, tblTYPE.TYPE, tblTYPE.VALUE
from tblNAME INNER JOIN
  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID
 
outputs:
 
NAME    TYPE    VALUE
001     1       20
002     1       25
003     2       50
004     2       55
005     3       70 
006     3       75

Open in new window

kak39Asked:
Who is Participating?
 
aaronakinConnect With a Mentor Commented:
Try this and see if it gives you what you need.  It works for me.
SELECT [NAME], [1] AS [START], [2] AS [STOP], [3] AS [PAUSE]
  FROM 
      (
      SELECT n.NAME_ID, n.[NAME], t.[TYPE], t.[VALUE]
        FROM tblNAME n
          INNER JOIN tblTYPE t ON n.NAME_ID = t.NAME_ID
      ) a
  PIVOT (SUM([VALUE]) FOR [TYPE] IN ([1], [2], [3])) AS PivotTable

Open in new window

0
 
Brendt HessSenior DBACommented:
How do you associate the start record with the End record, and either with a Pause record?

Will there always be a Pause record?
0
 
kak39Author Commented:
Sorry little confusing. The working query and output are in the code snipit. I need the TYPE field translated into (START, STOP, PAUSE) based on this info.

TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aaronakinCommented:
You'll need to use a PIVOT table for this.
SELECT n.[NAME], [1] AS [START], [2] AS [STOP], [3] AS [PAUSE]
  FROM tblNAME n
    INNER JOIN tblTYPE t ON n.NAME_ID = n.NAME_ID
PIVOT (SUM(t.[VALUE]) FOR t.[TYPE] IN ([1], [2], [3])) AS PivotTable

Open in new window

0
 
Brendt HessSenior DBACommented:
The translation is easy.  A simple CASE statement such as:

SELECT
    tblNAME.NAME,
    CASE tblTYPE.TYPE
        WHEN 1 THEN 'START'
        WHEN 2 THEN 'END'
        WHEN 3 THEN 'PAUSE'
    END AS [Type]
    tblTYPE.VALUE
from tblNAME INNER JOIN
  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID
 
But your data sample does not indicate how to get from there to the display output.
0
 
kak39Author Commented:
bhess, think you got it backwards. The info in the "CODE SNIPPET" section is what i'm currently running SQL and what i'm getting as an output. I need the output in the question

NAME     START    END     PAUSE
001        20           50         70
002        25           55         75
0
 
aaronakinCommented:
kak39, did you try the PIVOT script I provided?
0
 
kak39Author Commented:
yes, it had an error with the IND2USER field?
0
 
aaronakinCommented:
I'm not sure what field you are referring to.  Did you add it?  Can you try running the query I gave you as-is to see if it works?

Can you provide an actual sample of the data from tblNAME and tblTYPE, along with the column names so I can write you an accurate query if the other one I gave you doesn't work?
0
 
Brendt HessSenior DBACommented:
Let's start back at the beginning.

Can you give a data sample that provides all of the data you need to generate your sample output?  That is, the records for NAME 001 and NAME 002.  The data sample you have does not indicate any way to identify which records should be associated with NAME 001, since only one record contains that value.  I can make an assumption (that your sample data should have only NAME 001 and NAME 002 records all the way down), but that may not be a correct assumption.
0
 
kak39Author Commented:

Two tables:
 
tblNAME
-----------
NAME_ID   NAME
1         001
2         002
3         003
4         004
5         005
 
tblTYPE
--------------------
NAME_ID  TYPE  VALUE
1        1     20
1        2     50
1        3     70
2        1     25
2        2     55
2        3     75
 
The query below works, and this is the output I get. 
 
SELECT tblNAME.NAME, tblTYPE.TYPE, tblTYPE.VALUE
from tblNAME INNER JOIN
  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID
 
outputs:
 
NAME    TYPE    VALUE
001     1       20
001     2       50
001     3       70
002     1       25
002     2       55 
002     3       75
 
---------------------------
WHAT I NEED:
 
TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)
--------------------------------------------------------------------
NAME     START    END     PAUSE
001        20      50       70 
002        25      55       75

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.