Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Report formatting with UNION Clause Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 9:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:00 AM
Points: 8, Visits: 57
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


  Post Attachments 
Report.png (26 views, 11.32 KB)
Post #1447216
Posted Saturday, April 27, 2013 11:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
With that data, you can't group by what you've asked.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1447311
Posted Monday, April 29, 2013 11:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
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)

Post #1447680
Posted Tuesday, April 30, 2013 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:00 AM
Points: 8, Visits: 57
THANKS A TON, GEOFF..
Post #1447911
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse