April 26, 2013 at 9:07 pm
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
April 27, 2013 at 11:23 pm
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
April 29, 2013 at 11:51 am
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)
April 30, 2013 at 12:56 am
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