February 3, 2016 at 9:04 am
J Livingston SQL (2/3/2016)
ChrisM@Work (2/3/2016)
silverbullettruck (2/3/2016)
Yes, it would be ok to switch values for the values that exist multiple times for the same LOB with different values, but the values that only exist once per LOB need to be the same for both lines.Surely there will only be one line for a LOB which has 'values that only exist once per LOB'? Now you're confusing the hell out of me too.
Confusion will be my epitaph.
As I crawl a cracked and broken path
If we make it we can all sit back and laugh.
But I fear tomorrow I'll be crying
(KING CRIMSON "Epitaph")
Oh gosh - from A Young Person's Guide. A dinosaur with real feathers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2016 at 9:25 am
Here's the situation with the LOB = MNS. MNS has two different properties for the XLOB (ML and MS), for those two different XLOB values there are differing BPL values (MSUP% and MSEL%). When the legacy system references the LOB and looks for 'MNS' it differentiates between the two entries by their XLOB value. Based on the XLOB value it allows it to derive the proper BPL value. In the legacy system I cannot change the LOB value, I cannot change the XLOB value references and I cannot change how it interprets the BPL values. Because of that, I need to be able to have two rows for the MNS LOB so that each row has the distinct values for that LOB.
MNS --> ML --> MSEL%
MNS --> MS --> MSUP%
The data that I have is setup in a normalized form but the system that reads it needs it a less normalized form which is why I am trying to pivot this information from the normalized form to the denormalized form.
February 3, 2016 at 9:35 am
silverbullettruck (2/3/2016)
Here's the situation with the LOB = MNS. MNS has two different properties for the XLOB (ML and MS), for those two different XLOB values there are differing BPL values (MSUP% and MSEL%). When the legacy system references the LOB and looks for 'MNS' it differentiates between the two entries by their XLOB value. Based on the XLOB value it allows it to derive the proper BPL value. In the legacy system I cannot change the LOB value, I cannot change the XLOB value references and I cannot change how it interprets the BPL values. Because of that, I need to be able to have two rows for the MNS LOB so that each row has the distinct values for that LOB.MNS --> ML --> MSEL%
MNS --> MS --> MSUP%
The data that I have is setup in a normalized form but the system that reads it needs it a less normalized form which is why I am trying to pivot this information from the normalized form to the denormalized form.
I'll restate my original question - is this allowed instead:
MNS --> ML --> MSUP%
MNS --> MS --> MSEL%
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2016 at 9:39 am
Ah, that's what I was afraid of.
So it sounds like the values in some attributes are connected to specific values in other attributes. If there's a table somewhere that defines those relationships, then this might be feasible.
If not, getting consistent results will be very, very difficult.
Just to make sure I'm understanding this correctly, here's my current understanding:
1) All else being equal, when several attributes have multiple distinct values, you need one row for each possible combination.
2) However, some attributes are linked, and particular values for one tie to particular values in another. When this is the case, 1) is overridden, and only the matching values occur on a row together.
Is the above close, or way off?
Cheers!
February 3, 2016 at 10:41 am
silverbullettruck (2/3/2016)
Here's the situation with the LOB = MNS. MNS has two different properties for the XLOB (ML and MS), for those two different XLOB values there are differing BPL values (MSUP% and MSEL%). When the legacy system references the LOB and looks for 'MNS' it differentiates between the two entries by their XLOB value. Based on the XLOB value it allows it to derive the proper BPL value. ......MNS --> ML --> MSEL%
MNS --> MS --> MSUP%
......
I cannot see how from the data you have provided so far that the legacy system can determine the above selection......as Chris said earlier, why doesnt the legacy system go
MNS --> ML --> MSUP%
MNS --> MS --> MSEL%
is the data you are providing a true representation of the legacy structure ( all tables / all columns ) involved?....or have you "extracted some data" from a table and are now trying to create a report on it?
is there some hard coded logic in the legacy system that enables it to " determine" which route to take?
to be frank, as generous as you have been in providing sample scripts (BIG thanks)...I have a feeling that as Jacob mentions....something is missing somewhere.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 4, 2016 at 5:03 am
I believe I finally figured it out after hours of trial and error. In a previous post from ChrisM he said "lets add a row number" and that made me think of something...what if I added a row number to distinctly number the instances. An instance to me would be a unique combination of LOB and Attribute. This would give me "levels" that would allow me to dynamically iterate if additional duplicate mappings existed per LOB. Then I just had to pivot each "level" and union the levels together to get a single result set. Here's what I came up with. I used the original "#Test" table data that I provided originally in this post. You can ignore the CHAR(10) and CHAR(13)...I just did that to clean up the dynamic SQL results when I was troubleshooting. I am open to feedback on this if anyone sees issues or concerns.
DECLARE @FinalList VARCHAR(MAX) = '';
DECLARE @CurList CURSOR;
SET
@CurList = CURSOR FOR
SELECT DISTINCT Attribute
FROM #Test;
DECLARE @List VARCHAR(MAX) = '' ,
@CurListItem VARCHAR(MAX);
OPEN @CurList;
FETCH NEXT FROM @CurList INTO @CurListItem;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @List = @List + ',' + @CurListItem;
FETCH NEXT FROM @CurList INTO @CurListItem;
END;
SET @FinalList = RIGHT(@List, LEN(@List) - 1);
DECLARE @FullSQL AS VARCHAR(MAX) ,
@SelectSQL AS VARCHAR(12) = 'SELECT LOB,' ,
@SelectList VARCHAR(MAX) = @FinalList ,
@FromPivot AS VARCHAR(MAX) ,
@ClosePivot AS VARCHAR(12) = ' ) ) AS pvt' ,
@AlterViewSQL AS NVARCHAR(MAX);
DECLARE @MaxLevels INT;
WITH MaxLvlList ( InstanceID, LOB, Attribute, Value )
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY LOB, Attribute ORDER BY Attribute ) AS InstanceID ,
LOB ,
Attribute ,
Value
FROM #Test
)
SELECT @MaxLevels = MAX(mll.InstanceID)
FROM MaxLvlList AS mll;
DECLARE @CurLevel INT = 1;
DECLARE @SQLBase VARCHAR(8000) ,
@sql VARCHAR(8000) ,
@FinalSQL NVARCHAR(MAX) = '';
SET @SQLBase = 'WITH CrosswalkList ( InstanceID, LOB, Attribute, Value )
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY LOB, Attribute ORDER BY Attribute ) AS InstanceID ,
LOB,
Attribute,
Value
FROM #Test
)' + CHAR(13) + CHAR(10);
WHILE @CurLevel <= @MaxLevels
BEGIN
SET @FromPivot = CHAR(13) + CHAR(10) + 'FROM (
SELECT cl.LOB ,
cl.Attribute ,
cl.Value
FROM CrosswalkList AS cl
WHERE cl.InstanceID = ' + CAST(@CurLevel AS VARCHAR(10))
+ ') AS l PIVOT
( MAX(l.Value) FOR Attribute IN (';
SET @FullSQL = @SelectSQL + @SelectList + @FromPivot + @SelectList
+ @ClosePivot;
SET @FinalSQL = @FullSQL + CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13)
+ CHAR(10) + @FinalSQL;
SET @CurLevel = @CurLevel + 1;
END;
SET @FinalSQL = @SQLBase + 'SELECT DISTINCT Final.* FROM (' + LEFT(@FinalSQL,
LEN(@FinalSQL)
- LEN(CHAR(13)
+ CHAR(10)
+ 'UNION ALL'
+ CHAR(13)
+ CHAR(10)))
+ ') AS Final ORDER BY Final.LOB';
EXEC sys.sp_executesql @FinalSQL;
SELECT LOB ,
MMM ,
EFI ,
CSM ,
RA ,
COCM ,
ANM ,
CBRAM ,
HMS ,
MHPD ,
CAR ,
BDM ,
BPL ,
COBA ,
PT ,
XLO ,
WP ,
ES ,
ARA ,
SECTION ,
HX ,
OP ,
DP ,
CVG
FROM #FinalTest
ORDER BY LOB;
February 4, 2016 at 8:15 am
Or using the hardcoded crosstab model, this - which I find much easier to manipulate than dynamic SQL.
;WITH ExtraGroup AS (
SELECT LOB, Attribute, Value, grp = ROW_NUMBER() OVER (PARTITION BY LOB, Attribute ORDER BY Value)
FROM #test
),
CrossTab AS (
SELECT
LOB,
[XLO] = MAX(CASE WHEN Attribute = 'XLO' THEN Value ELSE '' END),
[BPL] = MAX(CASE WHEN Attribute = 'BPL' THEN Value ELSE NULL END),
[CAR] = MAX(CASE WHEN Attribute = 'CAR' THEN Value ELSE '' END),
[RA] = MAX(CASE WHEN Attribute = 'RA' THEN Value ELSE '' END),
[ARA] = MAX(CASE WHEN Attribute = 'ARA' THEN Value ELSE NULL END),
[PT] = MAX(CASE WHEN Attribute = 'PT' THEN Value ELSE '' END),
[COCM] = MAX(CASE WHEN Attribute = 'COCM' THEN Value ELSE '' END),
[CBRAM] = MAX(CASE WHEN Attribute = 'CBRAM' THEN Value ELSE '' END),
[EFI] = MAX(CASE WHEN Attribute = 'EFI' THEN Value ELSE '' END),
[DP] = MAX(CASE WHEN Attribute = 'DP' THEN Value ELSE '' END),
[CVG] = MAX(CASE WHEN Attribute = 'CVG' THEN Value ELSE NULL END),
[OP] = MAX(CASE WHEN Attribute = 'OP' THEN Value ELSE '' END),
[HX] = MAX(CASE WHEN Attribute = 'HX' THEN Value ELSE '' END),
[MHPD] = MAX(CASE WHEN Attribute = 'MHPD' THEN Value ELSE NULL END),
[SECTION] = MAX(CASE WHEN Attribute = 'SECTION' THEN Value ELSE NULL END),
[COBA] = MAX(CASE WHEN Attribute = 'COBA' THEN Value ELSE NULL END),
[HMS] = MAX(CASE WHEN Attribute = 'HMS' THEN Value ELSE NULL END),
[BDM] = MAX(CASE WHEN Attribute = 'BDM' THEN Value ELSE NULL END),
[MMM] = MAX(CASE WHEN Attribute = 'MMM' THEN Value ELSE NULL END),
[ANM] = MAX(CASE WHEN Attribute = 'ANM' THEN Value ELSE NULL END),
[CSM] = MAX(CASE WHEN Attribute = 'CSM' THEN Value ELSE NULL END),
[ES] = MAX(CASE WHEN Attribute = 'ES' THEN Value ELSE NULL END),
[WP] = MAX(CASE WHEN Attribute = 'WP' THEN Value ELSE NULL END)
FROM ExtraGroup
GROUP BY LOB,grp
)
SELECT DISTINCT * -- there are dupes
FROM CrossTab
ORDER BY LOB
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply