Solved

SQL Table Join issue

Posted on 2008-06-19
11
221 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:kak39
  • 4
  • 4
  • 3
11 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 21825030
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
 

Author Comment

by:kak39
ID: 21825112
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
 
LVL 11

Expert Comment

by:aaronakin
ID: 21825175
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
 
LVL 32

Expert Comment

by:bhess1
ID: 21825207
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
 

Author Comment

by:kak39
ID: 21825534
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 11

Expert Comment

by:aaronakin
ID: 21825545
kak39, did you try the PIVOT script I provided?
0
 

Author Comment

by:kak39
ID: 21825662
yes, it had an error with the IND2USER field?
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 21825724
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
 
LVL 32

Expert Comment

by:bhess1
ID: 21825875
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
 

Author Comment

by:kak39
ID: 21830588

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
 
LVL 11

Accepted Solution

by:
aaronakin earned 150 total points
ID: 21831462
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now