February 2, 2016 at 1:37 pm
Can we push the reset button? I think I derailed people by mistake. Here's a smaller set of data and a before and after of the result set. I am hoping this makes it clearer for what I am looking for.
--Comes in looking like this
/*
LOBAttribute Value
------ -------------- --------
MNSXLOC ML
MNSXLOC MS
MNSXLOC ML
MNSXLOC MS
MNSBPLMSEL%
MNSBPLMSUP%
MNSBPLMSEL%
MNSBPLMSUP%
MNSARANULL
MNSARANULL
MNSARANULL
MNSARANULL
MNSCREN
MNSCREN
MNSCREN
MNSCREN
*/
CREATE TABLE #tablesource ( [LOB] varchar(10), [Attribute] varchar(50), [Value] varchar(8000) )
INSERT INTO #tablesource
VALUES
( 'COM', 'XLOC', 'PS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'COM', 'BPL', NULL ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'COM', 'ARA', 'COM' ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'COM', 'CRE', 'Y' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' )
DROP TABLE #temptable
--Needs to look like this
/*
LOBXLOC BPLCREARA
----- ------ ------ ---------- ------
COMPS NULL YCOM
MNSML MSEL% NNULL
MNSMS MSUP% NNULL
*/
CREATE TABLE #tabledest ( [LOB] varchar(10), [XLOC] varchar(2), [BPL] varchar(15), [CRE] char, [ARA] varchar(3) )
INSERT INTO #tabledest
VALUES
( 'COM', 'PS', NULL, 'Y', 'COM' ),
( 'MNS', 'ML', 'MSEL%', 'N', NULL ),
( 'MNS', 'MS', 'MSUP%', 'N', NULL )
DROP TABLE #tabledest
February 2, 2016 at 1:52 pm
silverbullettruck (2/2/2016)
Can we push the reset button? I think I derailed people by mistake. Here's a smaller set of data and a before and after of the result set. I am hoping this makes it clearer for what I am looking for.
--Comes in looking like this/*
LOBAttribute Value
------ -------------- --------
MNSXLOC ML
MNSXLOC MS
MNSXLOC ML
MNSXLOC MS
MNSBPLMSEL%
MNSBPLMSUP%
MNSBPLMSEL%
MNSBPLMSUP%
MNSARANULL
MNSARANULL
MNSARANULL
MNSARANULL
MNSCREN
MNSCREN
MNSCREN
MNSCREN
*/
CREATE TABLE #tablesource ( [LOB] varchar(10), [Attribute] varchar(50), [Value] varchar(10) )
INSERT INTO #tablesource
VALUES
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' )
DROP TABLE #tablesource
--Needs to look like this
/*
LOBXLOC BPLCREARA
----- ------ ------ ---------- ------
COMPS NULL YCOM
MNSML MSEL% NNULL
MNSMS MSUP% NNULL
*/
CREATE TABLE #tabledest ( [LOB] varchar(10), [XLOC] varchar(2), [BPL] varchar(15), [CRE] char, [ARA] varchar(3) )
INSERT INTO #tabledest
VALUES
( 'COM', 'PS', NULL, 'Y', 'COM' ),
( 'MNS', 'ML', 'MSEL%', 'N', NULL ),
( 'MNS', 'MS', 'MSUP%', 'N', NULL )
DROP TABLE #tabledest
care to revisit your sample data....dont see any LOB with value "COM"
you say " comes in looking like this"....do we assume this is being produced externally and you are importing?
________________________________________________________________
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 2:02 pm
sorry about missing the 'COM' I edited the previous post and added it in.
As for the "comes in looking like this", this is not an import. It was how I was referring to how that is what the source data looks like.
February 3, 2016 at 6:22 am
silverbullettruck (2/2/2016)
Can we push the reset button? I think I derailed people by mistake. Here's a smaller set of data and a before and after of the result set. I am hoping this makes it clearer for what I am looking for.
--Comes in looking like this/*
LOBAttribute Value
------ -------------- --------
MNSXLOC ML
MNSXLOC MS
MNSXLOC ML
MNSXLOC MS
MNSBPLMSEL%
MNSBPLMSUP%
MNSBPLMSEL%
MNSBPLMSUP%
MNSARANULL
MNSARANULL
MNSARANULL
MNSARANULL
MNSCREN
MNSCREN
MNSCREN
MNSCREN
*/
CREATE TABLE #tablesource ( [LOB] varchar(10), [Attribute] varchar(50), [Value] varchar(8000) )
INSERT INTO #tablesource
VALUES
( 'COM', 'XLOC', 'PS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'COM', 'BPL', NULL ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'COM', 'ARA', 'COM' ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'COM', 'CRE', 'Y' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' )
DROP TABLE #temptable
--Needs to look like this
/*
LOBXLOC BPLCREARA
----- ------ ------ ---------- ------
COMPS NULL YCOM
MNSML MSEL% NNULL
MNSMS MSUP% NNULL
*/
CREATE TABLE #tabledest ( [LOB] varchar(10), [XLOC] varchar(2), [BPL] varchar(15), [CRE] char, [ARA] varchar(3) )
INSERT INTO #tabledest
VALUES
( 'COM', 'PS', NULL, 'Y', 'COM' ),
( 'MNS', 'ML', 'MSEL%', 'N', NULL ),
( 'MNS', 'MS', 'MSUP%', 'N', NULL )
DROP TABLE #tabledest
me thinks I aint quite following your thoughts 🙂
you said earlier
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.
in the sample above you only have one unique combo of MNS/N (lob/cre)....yet you require it in both rows?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 3, 2016 at 7:03 am
The uniqueness is across all of the attributes as a set. So in the example, there is a unique combination for MNS across XLOC, BPL, CRE, and ARA. For MNS the unique attribute 'XLOC' has two different values for MNS that requires me to generate two rows, one for each of the distinct attribute values ('MSEL%' and 'MSUP%').
February 3, 2016 at 7:35 am
silverbullettruck (2/3/2016)
The uniqueness is across all of the attributes as a set. So in the example, there is a unique combination for MNS across XLOC, BPL, CRE, and ARA. For MNS the unique attribute 'XLOC' has two different values for MNS that requires me to generate two rows, one for each of the distinct attribute values ('MSEL%' and 'MSUP%').
Looking at your most recent example, let's add a row number to make the set easier to dissect:
[font="Courier New"]
RowIDLOB XLOC BPL CRE ARA
----- ----- ------ ------ ---------- ------
1 COM PS NULL Y COM
2 MNS ML MSEL% N NULL
3 MNS MS MSUP% N NULL
[/font]
So, for row 2, LOB = 'MNS', XLOC = 'ML' and BPL = 'MSEL%'. What's preventing XLOC from being 'MS' instead?
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 8:05 am
MNS has two XLOC's so it makes it necessary to have both an 'MS' and 'ML' that is what is keeping the 'ML' from being an 'MS' or vice versa.
February 3, 2016 at 8:10 am
silverbullettruck (2/3/2016)
MNS has two XLOC's so it makes it necessary to have both an 'MS' and 'ML' that is what is keeping the 'ML' from being an 'MS' or vice versa.
So this would be okay - or not:
[font="Courier New"]
RowIDLOB XLOC BPL CRE ARA
----- ----- ------ ------ ---------- ------
1 COM PS NULL Y COM
2 MNS ML MSUP% N NULL
3 MNS MS MSEL% N NULL
[/font]
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 8:12 am
Yes, that would be ok.
February 3, 2016 at 8:17 am
ok, I know the following isnt the solution you have requested.
But I am struggling to come up with a solution that exactly matches your requirements, I hope someone else will be able to assist you.
On a personal thought, I dont follow how your "production" systems manage this data.....you have said that it cannot be changed because of these.
anyways
here below lies an alternative solution (thanks to Craig in previous post for the dynamic attributes)
I would also ask for all other readers that you provide your required result set from the sample data below.
Good Luck, I wish you well with this.
CREATE TABLE #tablesource ( [LOB] varchar(10), [Attribute] varchar(50), [Value] varchar(8000) )
INSERT INTO #tablesource
VALUES
( 'COM', 'XLOC', 'PS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'MNS', 'XLOC', 'ML' ),
( 'MNS', 'XLOC', 'MS' ),
( 'COM', 'BPL', NULL ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'MNS', 'BPL', 'MSEL%' ),
( 'MNS', 'BPL', 'MSUP%' ),
( 'COM', 'ARA', 'COM' ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'MNS', 'ARA', NULL ),
( 'COM', 'CRE', 'Y' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
( 'MNS', 'CRE', 'N' ),
-- additional rows
( 'ABC', 'JLS', '1' ),
( 'ABC', 'JLS', '2' ),
( 'ABC', 'JLS', '3' ),
( 'ABC', 'JLS', '4' ),
( 'DOH', 'ABC', '777' ),
( 'DOH', 'ABC', '666' ),
( 'DOH', 'XLOC', 'ML' ),
( 'DOH', 'XLOC', 'MS' ),
( 'DOH', 'XLOC', 'MT' ),
( 'DOH', 'CRE', 'OMG' ),
( 'DOH', 'CRE', 'WTF' )
DECLARE @SQL NVARCHAR(MAX);
with cte as (
SELECT DISTINCT lob, attribute, value
FROM #tablesource
)
SELECT
lob, attribute,
STUFF(
(
SELECT ' | ' + value
FROM cte p2
WHERE p1.lob = p2.lob and p1.attribute = p2.attribute
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1,3, '') res
INTO #temp
FROM cte p1
GROUP BY lob, p1.attribute
SELECT @SQL = 'SELECT LOB, '
+ STUFF((SELECT ',' + CHAR(13) + CHAR(10)
+ 'MAX(CASE WHEN Attribute = ' + CHAR(39) + Attribute
+ CHAR(39) + ' THEN res ELSE NULL END) AS ['
+ Attribute + ']'
FROM ( SELECT DISTINCT Attribute
FROM #temp
) a
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '')
+ ' FROM #temp GROUP BY LOB;';
EXECUTE sp_executesql @SQL;
DROP TABLE #tablesource;
DROP TABLE #temp;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 3, 2016 at 8:20 am
silverbullettruck (2/3/2016)
Yes, that would be ok.
So it's okay to switch values between rows of the same LOB? That makes it easier - but it's still quite fiddly coding. One way would be a new column for your GROUP BY, and a calculation of the max number of XLOCs per 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
February 3, 2016 at 8:35 am
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.
February 3, 2016 at 8:35 am
As much as I hate being a broken record (or beating dead horses), I'm still really confused as to the logic behind a LOB with two unique values for each of two attributes getting two rows, but a LOB with 4 unique values in one attribute and two in another getting 8 rows.
Getting to a point where we understand the underlying requirements would be great, but the requirements have been rather fuzzy so far.
In short, I'm trying to find consistent desired results for each of the following:
CREATE TABLE #test (LOB varchar(10),
attribute varchar(10),
value varchar(10)
);
INSERT INTO #test VALUES
('MNS','XLO','MS'),
('MNS','XLO','ML'),
('MNS','BPL','MSUP%'),
('MNS','BPL','MSEL%');
--What's the desired result for the above?
TRUNCATE TABLE #test;
INSERT INTO #test VALUES
('MNS','XLO','MS'),
('MNS','XLO','ML'),
('MNS','XLO','MR'),
('MNS','BPL','MSUP%'),
('MNS','BPL','MSEL%');
--Now what's the desired result for the above data?
DROP TABLE #test;
The logic underlying the desired results so far has been rather elusive. I know this is the same thing we've been trying to hash out, but we've never managed to get desired results for each of these types of inputs (same number of unique values for the attributes with multiple values versus differing numbers of unique values for the attributes with multiple values) at the same time.
Hopefully if we can get desired results for both of them at the same time, we can more easily see the underlying logic (or if the requirements are inconsistent, that too).
Cheers!
February 3, 2016 at 8:44 am
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.
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 8:58 am
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")
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply