Solved

SQL Table Join issue

Posted on 2008-06-19
11
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unable to save view in SSMS 21 81
why sql server only update some statistics in the database ? 3 40
Sql Server group by 10 50
What is this datetime? 1 28
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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