CODEWITHSUNDEEP
sqlunion
mholmes3038
mholmes3038

Reputation: 55

SQL - UNION - How to Manipulate Data Returned

Trying to figure out how to get my data to return the SELECT data first and then display the UNION data below. Currently it is returning the UNION data first two rows and SELECT data after. I need the SELECT on Top and the UNION on bottom. Tried a few ways but nothing is working.

 SELECT 
                    Record_Time_Stamp AS 'Record_Time_Stamp', 
                    Car_ID, 
                    Commodity, 
                    CAST(Transaction_Num AS NVARCHAR(MAX)) AS 'Transaction_Num', 
                    CAST(Weight_Out AS NVARCHAR(MAX)) AS 'Gross_Wt', 
                    CAST(Weight_In AS NVARCHAR(MAX)) AS 'Tare_Wt', 
                    CAST(abs(Weight_In - Weight_Out) AS NVARCHAR) AS 'Net_Wt'
                FROM 
                    dbo.Transactions_Truck 
                WHERE 
                    (Date_Weighed_Out between @BegDate and @EndDate) AND
                    Vendor = @Vendor
            UNION
                SELECT
                        GETDATE() AS Record_Time_Stamp,
                        '' AS Car_ID,
                        '' AS Commodity,
                        'Total Tons' AS 'Transaction_Num',
                        '' AS 'Gross_Wt',
                        '' AS 'Tare_Wt',
                        @Total_Tons AS 'Net_Wt'
                    FROM
                        dbo.Transactions_Truck 
            UNION
                SELECT
                    GETDATE() AS Record_Time_Stamp,
                    '' AS Car_ID,
                    '' AS Commodity,
                    'Total Lbs.' AS 'Transaction_Num',
                    '' AS 'Gross_Wt',
                    '' AS 'Tare_Wt',
                    @Total_Lbs AS 'Net_Wt'
                FROM
                    dbo.Transactions_Truck 
                ORDER BY 
                    Transaction_Num

Upvotes: 1

Views: 154

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Your query, as written, really makes no sense. Apart from certain grating habits (such as using single quotes for column aliases, not having lengths for VARCHAR()), it is highly inefficient. It is creating multiple rows for the last two subqueries, only to remove them in the UNION.

In general, it is better to use UNION ALL rather than UNION. I think you want something like this:

SELECT Record_Time_Stamp, Car_ID, Commodity, 
       CAST(Transaction_Num AS NVARCHAR(MAX)) AS Transaction_Num, 
       CAST(Weight_Out AS NVARCHAR(MAX)) AS Gross_Wt, 
       CAST(Weight_In AS NVARCHAR(MAX)) AS Tare_Wt, 
       CAST(abs(Weight_In - Weight_Out) AS NVARCHAR(32)) AS Net_Wt
FROM dbo.Transactions_Truck 
WHERE (Date_Weighed_Out between @BegDate and @EndDate) AND
      Vendor = @Vendor
UNION ALL
SELECT GETDATE() AS Record_Time_Stamp, '' AS Car_ID, '' AS Commodity,
       'Total Tons' AS Transaction_Num,
       '' AS 'Gross_Wt', '' AS Tare_Wt, @Total_Tons AS Net_Wt
UNION ALL
SELECT GETDATE() AS Record_Time_Stamp, '' AS Car_ID,'' AS Commodity,
       'Total Lbs.' AS Transaction_Num, '' AS Gross_Wt, '' AS Tare_Wt,
        @Total_Lbs AS Net_Wt;

This will probably do what you want in practice. However, I would suggest adding an ORDER BY clause such as:

ORDER BY (CASE WHEN Car_ID = '' THEN 2 ELSE 1 END),
         Transaction_Num

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could add own column:

SELECT ..., 1 AS query_num
FROM ...
UNION 
SELECT ..., 2 AS query_num
FROM ...
UNION
SELECT ..., 3 AS query_num
FROM ...
ORDER BY query_num, ...

Keep in mind that adding column will circumvent UNION distinctness, so you could change it UNION ALL.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Introduce an artificial sort key column (I'll call it MySortKey in the code sample below) to keep the two selects separated.

SELECT 
                Record_Time_Stamp AS 'Record_Time_Stamp', 
                Car_ID, 
                Commodity, 
                CAST(Transaction_Num AS NVARCHAR(MAX)) AS 'Transaction_Num', 
                CAST(Weight_Out AS NVARCHAR(MAX)) AS 'Gross_Wt', 
                CAST(Weight_In AS NVARCHAR(MAX)) AS 'Tare_Wt', 
                CAST(abs(Weight_In - Weight_Out) AS NVARCHAR) AS 'Net_Wt',
                1 AS MySortKey
            FROM 
                dbo.Transactions_Truck 
            WHERE 
                (Date_Weighed_Out between @BegDate and @EndDate) AND
                Vendor = @Vendor
        UNION
            SELECT
                    GETDATE() AS Record_Time_Stamp,
                    '' AS Car_ID,
                    '' AS Commodity,
                    'Total Tons' AS 'Transaction_Num',
                    '' AS 'Gross_Wt',
                    '' AS 'Tare_Wt',
                    @Total_Tons AS 'Net_Wt',
                    2 AS MySortKey
                FROM
                    dbo.Transactions_Truck 
        UNION
            SELECT
                GETDATE() AS Record_Time_Stamp,
                '' AS Car_ID,
                '' AS Commodity,
                'Total Lbs.' AS 'Transaction_Num',
                '' AS 'Gross_Wt',
                '' AS 'Tare_Wt',
                @Total_Lbs AS 'Net_Wt'
            FROM
                dbo.Transactions_Truck 
            ORDER BY 
                MySortKey, Transaction_Num

Upvotes: 1

Related Questions