February 2, 2016 at 7:42 am
Yes, that would be a valid assumption for the LOB/Attribute relationship.
These are the only current fields in the data today, but as additional attributes are added it would need to add additional fields. So if I created a new LOB/attribute record called "NewThing" it would need to create a column for 'NewThing' and display the value for the LOBs that have that attribute with its value.
As for ID/Date availability per record, there is nothing like that in the data.
February 2, 2016 at 8:27 am
ChrisM, I was looking at your response/solution and it looks close, but it's not handing the duplicate records (where there are two instances of MNS with different XLO values). That's where I have been stumped on how to get around that since there could be duplicate or more for any given LOB value.
February 2, 2016 at 9:36 am
is this "your" table design.....can you make any changes to it?
going back to your original post...what results are you expecting from such a scenario as the following?
RBR Attribute Value
------- ---------- ----------
ONR XLOC ML
ONR XLOC MS
ONR XLOC ML
ONR XLOC MS
ONR CRE AA
ONR CRE BB
ONR CRE CC
ONR CRE DD
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 2, 2016 at 9:47 am
I can't change the table design as too many production systems would be impacted.
As for what I would expect for a result...here's what I would hope to see
LOB XLOC CRE
----- ------- ------
ONR ML AA
ONR ML BB
ONR ML CC
ONR ML DD
ONR MS AA
ONR MS BB
ONR MS CC
ONR MS DD
February 2, 2016 at 10:06 am
A little late to the party, but unless I'm missing something (always possible :-))the desired results you gave for J Livingston's sample data seem inconsistent with the desired results from your sample data earlier.
The desired results from J Livingston's sample data include every combination of the distinct values of each attribute, which was not how the earlier results did it.
In the earlier posted results for LOB='MNS', for example, there were two distinct values for XLO, and two distinct values for BPL, but instead of having each combination of those two in the desired result (so four rows), there were only two rows.
If we can get that cleared up, it should be fairly straightforward from there.
Cheers!
February 2, 2016 at 10:14 am
Looks like Jacob bet me to it....was going to ask same question.
I am also wondering what business requirement you are trying to answer.
with possibly unlimited attributes each having unlimited values.....what will your end report actually demonstrate?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 2, 2016 at 10:53 am
Jacob, due to J Livingston's data set, there would need to be 4 rows for each distinct LOB/XLOC/CRE combination. In the example i provided earlier the MNS had two rows because the all of the data was repeated except the BPL field which had two distinct values for MNS, so since the other data was the same i only resulted in 2 records. In the example J Livingston provided there were two distinct XLOC values and 4 distinct CRE values so I would expect 8 total results.
February 2, 2016 at 10:58 am
Not all the other values were repeated. There were also two distinct values for XLO for that LOB
Jacob Wilkins (2/2/2016)
...In the earlier posted results for LOB='MNS', for example, there were two distinct values for XLO[/i], and two distinct values for BPL, but instead of having each combination of those two in the desired result (so four rows), there were only two rows.
...
Based on your most recent description, though, it sounds like that was just an oversight, and the goal is to get every combination of distinct values. I'll work under that assumption for now and try to get some code together, but let me know if I'm still misunderstanding something.
Cheers!
February 2, 2016 at 11:06 am
For a business requirement, I am trying to satisfy a need for each LOB to be listed with distinct rows for each unique attribute and it's unique value with the attribute name as the column and the attribute values listed in relation to the LOB/Attribute combination.
February 2, 2016 at 11:08 am
I would like to just apologize right now for describing this poorly. I do appreciate whatever assistance you can provide. 🙂
February 2, 2016 at 11:27 am
silverbullettruck (2/2/2016)
I would like to just apologize right now for describing this poorly. I do appreciate whatever assistance you can provide. 🙂
No worries, it's a fairly involved requirement, so such things are bound to happen.
To start simply, for the sample data you posted, which of the following is the correct result for LOB='MNS' (I've removed the values for columns other than XLO and BPL, since they were all the same, and it's easier to see this way)?
--Option 1 (the original)
--Note that there are two different values for both
--XLO (ML and MS) and BPL (MSEL% and MSUP%)
INSERT INTO #FinalTest VALUES (LOB,XLO,BPL)
( 'MNS', 'ML', 'MSEL%'),
( 'MNS', 'MS', 'MSUP%');
--Option 2
--This is in line with what you did
--for J Livingston's sample data
INSERT INTO #FinalTest VALUES (LOB,XLO,BPL)
( 'MNS', 'ML', 'MSEL%'),
( 'MNS', 'ML', 'MSUP%'),
( 'MNS', 'MS', 'MSEL%'),
( 'MNS', 'MS', 'MSUP%');
Cheers!
February 2, 2016 at 12:25 pm
Option 1 is what I am looking for.
February 2, 2016 at 12:46 pm
just for fun......is the following a correct answer ??
CREATE TABLE #Test (
RBR VARCHAR(3) NULL,
Attribute VARCHAR(10) NULL,
Value VARCHAR(2)
)
INSERT INTO #Test VALUES ('ONR','XLOC','ML')
INSERT INTO #Test VALUES ('ONR','XLOC','MS')
INSERT INTO #Test VALUES ('ONR','XLOC','ML')
INSERT INTO #Test VALUES ('ONR','XLOC','MS')
INSERT INTO #Test VALUES ('ONR','CRE','AA')
INSERT INTO #Test VALUES ('ONR','CRE','BB')
INSERT INTO #Test VALUES ('ONR','CRE','CC')
INSERT INTO #Test VALUES ('ONR','CRE','DD')
INSERT INTO #Test VALUES ('ONR','JLS','01')
INSERT INTO #Test VALUES ('ONR','JLS','02')
INSERT INTO #Test VALUES ('ONR','JLS','03')
INSERT INTO #Test VALUES ('ONR','SBB','99')
INSERT INTO #Test VALUES ('ONR','SBB','98')
INSERT INTO #Test VALUES ('ONR','TST','HI')
;
WITH
xloc as (
SELECT DISTINCT RBR, Value AS XLOC
FROM #Test
WHERE (Attribute = 'XLOC'))
,
cre as (
SELECT DISTINCT RBR, Value AS CRE
FROM #Test
WHERE (Attribute = 'CRE'))
,
jls as (
SELECT DISTINCT RBR, Value AS JLS
FROM #Test
WHERE (Attribute = 'JLS'))
,
sbb as (
SELECT DISTINCT RBR, Value AS SBB
FROM #Test
WHERE (Attribute = 'SBB'))
,
tst as (
SELECT DISTINCT RBR, Value AS TST
FROM #Test
WHERE (Attribute = 'TST'))
SELECT
xloc.RBR
, xloc.XLOC
, cre.CRE
, jls.JLS
, sbb.SBB
, tst.TST
FROM xloc INNER JOIN
cre ON xloc.RBR = cre.RBR
INNER JOIN
jls ON xloc.RBR = jls.RBR
INNER JOIN
sbb ON xloc.RBR = sbb.RBR
INNER JOIN
tst ON xloc.RBR = tst.RBR
ORDER BY xloc.RBR
, xloc.XLOC
, cre.CRE
, jls.JLS
, sbb.SBB
, tst.TST
DROP TABLE #Test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 2, 2016 at 12:50 pm
In that case I'm still a bit confused, I fear.
Why do we want all combinations of distinct values with J Livingston's data (4 distinct values for one attribute, 2 for another, so 8 total rows), but not the data in my previous post (2 distinct values for one attribute, 2 for another, but only 2 rows instead of the expected 4)?
I'm sure there's some simple rule underlying all this, but I haven't figured it out yet 🙂
Cheers!
February 2, 2016 at 1:25 pm
to pile on with the confusion if I may
what results required for this set?
CREATE TABLE #Test (
RBR VARCHAR(3) NULL,
Attribute VARCHAR(10) NULL,
Value VARCHAR(2)
)
INSERT INTO #Test VALUES ('ONR','XLOC','ML')
INSERT INTO #Test VALUES ('ONR','XLOC','MS')
INSERT INTO #Test VALUES ('ONR','CRE','AA')
INSERT INTO #Test VALUES ('ONR','CRE','BB')
INSERT INTO #Test VALUES ('ONR','CRE','CC')
INSERT INTO #Test VALUES ('ONR','JLS','01')
INSERT INTO #Test VALUES ('ONR','JLS','02')
INSERT INTO #Test VALUES ('ONR','JLS','03')
INSERT INTO #Test VALUES ('ONR','SBB','99')
INSERT INTO #Test VALUES ('ONR','SBB','98')
INSERT INTO #Test VALUES ('ONR','TST','HI')
-- add some new records with a few new attributes
INSERT INTO #Test VALUES ('ABC','XLOC','1')
INSERT INTO #Test VALUES ('ABC','CRE','2')
INSERT INTO #Test VALUES ('ABC','OBJ','3')
INSERT INTO #Test VALUES ('ABC','DOH','1')
INSERT INTO #Test VALUES ('ABC','PR','2')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply