Solved

SQL query, table's field to field's value

Posted on 2004-10-02
7
280 Views
Last Modified: 2008-03-10
What I'm going to ask a bit tricky.

I have 2 tables, m_status and m_trans

m_status
----------
code     status
1          Car
2          Bus
3          Lorry



m_trans
---------
id        car        bus           lorry        train
1         10         5              3             23
2         4           8              1             3
3         7           12            23            7
4         5           2              3              8

My question is how do I sum the value in m_trans table where m_trans table fields name equivalent to m_status.status values (Car, Bus and Lorry)?
0
Comment
Question by:showbix
[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
  • 2
7 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12208693
I'm not certain I understand the results you want, but I'm guessing you mean this:

SELECT TotalCar, TotalBus, TotalLorry, TotalTrain FROM
(SELECT Sum(Car) TotalCar FROM m_trans) as c,
(SELECT Sum(Bus) TotalBus FROM m_trans) as b,
(SELECT Sum(Lorry) TotalLorry FROM m_trans) as l,
(SELECT Sum(Train) TotalTrain FROM m_trans) as t
0
 
LVL 1

Expert Comment

by:elchukg
ID: 12208873
Create and use the following procedure to sum the value in m_trans table where m_trans table fields name equivalent to m_status.status values

CREATE   PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      IF @statusname = 'Car'
      BEGIN
      EXEC ('SELECT sum(car) As Total_Car  FROM m_trans '
            )
      PRINT ' '
      END
      IF @statusname = 'Bus'
      BEGIN
      EXEC ('SELECT sum(bus) As Total_Bus  FROM m_trans '
            )
      PRINT ' '
      END      IF @statusname = 'Lorry'
      BEGIN
      EXEC ('SELECT sum(lorry) As Total_Lorry  FROM m_trans '
            )
      PRINT ' '
      END      IF @statusname = 'Train'
      BEGIN
      EXEC ('SELECT sum(train) As Total_Train  FROM m_trans '
            )
      PRINT ' '
      END   END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor

Hope this is what you were looking for.

Glen Elchuk
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12208893
Boy, I don't know if I could get any goofier than with my first suggestion. If you like those results, all you need is:

SELECT Sum(Car) TotalCar, Sum(Bus) TotalBus, Sum(Lorry) TotalLorry, Sum(Train) TotalTrain FROM m_trans
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 1

Expert Comment

by:elchukg
ID: 12210196
Here is a shorter version of my procedure

CREATE    PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  

      EXEC ('SELECT sum(' + @statusname + ') As Total_' + @statusname + '  FROM m_trans '
            )
      PRINT ' '
  END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor


I hope this helps,

Glen Elchuk

0
 
LVL 1

Accepted Solution

by:
elchukg earned 75 total points
ID: 12210248
If you want the results on one line as one query use the following procedure,

CREATE PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname,
        @SumValues sysname
Set @SumValues = 'sum(ID)AS Total'
FETCH NEXT FROM tTotals_cursor INTO @statusname
Set @SumValues = 'SUM(' + RTRIM(@statusname) + ') As Total_' + RTRIM(@statusname)
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  

      Set @SumValues = @SumValues + ', sum(' + RTRIM(@statusname) + ') As Total_' + RTRIM(@statusname)
           
     
  END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
EXEC ('SELECT ' + @SumValues  + '  FROM m_trans ')
   
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor
0
 

Author Comment

by:showbix
ID: 12210250
Hi Elchuk.
What is FETCH_STATUS? What does the -1 and -2 means?
By the way, your solution works great.
Thanks.
0
 
LVL 1

Expert Comment

by:elchukg
ID: 12214398
Hi showbix,

@@FETCH_STATUS
Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Return value Description
0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing.

Glad I could heip,


Glen Elchuk

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 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