SQL Report formatting with UNION Clause

  • I have an sql like this:

    SELECT 1 AS GROUPID, 'A' AS GROUPNAME, ''GROUPID, ''GROUPNAME

    UNION

    SELECT 2 AS GROUPID, 'D' AS GROUPNAME, ''GROUPID, ''GROUPNAME

    UNION

    SELECT 3 AS GROUPID, 'B' AS GROUPNAME, ''GROUPID, ''GROUPNAME

    UNION

    SELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID, 'A' GROUPNAME

    UNION

    SELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID, 'AA'GROUPNAME

    UNION

    SELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID, 'AA'GROUPNAME

    UNION

    SELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID, 'A'GROUPNAME

    UNION

    SELECT 2 AS GROUPID, 'AD' AS GROUPNAME, ''GROUPID, ''GROUPNAME

    How to display the report as shown in the attachment....Alignment based on GroupID...Please help

  • With that data, you can't group by what you've asked.

    --------
    For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • I believe this is not so much a question of grouping data as it is in presenting it. It would appear you want the left two columns sorted by GroupID and the right two columns sorted by GroupID, omitting the non-zero rows from each. However, you want an extra row (or rows) whenever necessary to make sure that each GroupID has the same number of rows on the left and on the right.

    Although this could probably be best solved with the presentation layer of the report (e.g. in Reporting Services), it is possible to achieve the result using a SQL approach. To get from your raw data sample to the end result requires three phases:

    1. The secondary instances of "GroupID" and "GroupName" must have different column names than the first two instances. It just isn't a valid table design to have the same names for multiple columns. In the first CTE of my solution below, I have named the secondary instances GROUPID2 AND GROUPNAME2.

    2. The rows within each set of distinct GroupID values must be numbered, and you must get a row count for both GROUPID and GROUPID2. The second CTE in the solution below adds two new columns: LeftRowNumber (a count of the rows for GROUPID) and RightRowNumber (a count of the rows for GROUPID2).

    3. In the final step, the two halves of the original data are divided and treated as two separate sets of data: LeftData and RightData. The are related to one another on LeftData.GROUPID = RightData.GROUPID2 and on LeftData.LeftRowNumber = RightData.RightRowNumber. A WHERE clause removes the rows with a 0 on either the left or the right. The use of a full join allows unmatched rows on both sides to be included in the results. By ordering the rows on GroupID and row number (using the ISNULL() function to get the value on the left or right when the other one is null), the results can look almost identical to your sample screenshot.

    With

    RawData as

    ( SELECT 1 AS GROUPID, 'A' AS GROUPNAME, ''GROUPID2, ''GROUPNAME2

    UNION SELECT 2 AS GROUPID, 'D' AS GROUPNAME, ''GROUPID2, ''GROUPNAME2

    UNION SELECT 3 AS GROUPID, 'B' AS GROUPNAME, ''GROUPID2, ''GROUPNAME2

    UNION SELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID2, 'A' GROUPNAME2

    UNION SELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID2, 'AA'GROUPNAME2

    UNION SELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID2, 'AA'GROUPNAME2

    UNION SELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID2, 'A'GROUPNAME2

    UNION SELECT 2 AS GROUPID, 'AD' AS GROUPNAME, ''GROUPID2, ''GROUPNAME2),

    NumberedData as

    (select

    row_number() over (partition by GroupID order by GroupID) as LeftRowNumber,

    GROUPID, GROUPNAME,

    row_number() over (partition by GroupID2 order by GroupID2) as RightRowNumber,

    GROUPID2, GROUPNAME2

    from RawData)

    select

    LeftData.GROUPID,

    LeftData.GROUPNAME,

    RightData.GROUPID2,

    RightData.GROUPNAME2

    from

    NumberedData as LeftData full join

    NumberedData as RightData on LeftData.GROUPID = RightData.GROUPID2

    and LeftData.LeftRowNumber = RightData.RightRowNumber

    where

    LeftData.GROUPID <> 0 or RightData.GroupID2 <> 0

    order by

    isnull(LeftData.GROUPID, RightData.GROUPID2),

    isnull(lLftData.LeftRowNumber, RightData.RightRowNumber)

  • THANKS A TON, GEOFF..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply