Solved

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

Posted on 2004-10-02
7
267 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
  • 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
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.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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