Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Report formatting with UNION Clause


SQL Report formatting with UNION Clause

Author
Message
Boby B Jacob
Boby B Jacob
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 77
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
Attachments
Report.png (26 views, 11.00 KB)
Calibear
Calibear
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
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
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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)


Boby B Jacob
Boby B Jacob
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 77
THANKS A TON, GEOFF..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search