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
Medium Priority
293 Views
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
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
• 4
• 2

LVL 15

Expert Comment

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

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

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

LVL 1

Expert Comment

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

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

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

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.

Glen Elchuk

0

## Featured Post

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.
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll