Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-10-02
7
Medium Priority
?
293 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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